Dan, did you commit this query to CVS?
I'll commit it tonight. I'm at work now, and I'm stuck behind a firewall, so I don't have access to cvs.
Also, you might check out the way I rewrote /intranet/employees/admin/index.tcl and friends. I was able to get away from the horribly complex set of unions that resulted from mechanical translation.
I'll take a look at it. Ben fixed one of the intranet queries for me that had 16 unioned selects. It turned out that all of the outer-joined components were non-null, so in actuality, only two of the 16 outer join cases would actually occur. The fixed query had only two select clauses.
My rewrite trick won't apply to all complex outer joins, mechanically unfolded horrors like your example here are still going to be necessary at times.
When I was porting intranet, I suspected that many of the queries could be simplified, but given the size of the task I opted for the mechanical porting approach. Usually it went copy, paste, paste, paste, add not exits clauses and remove dangling tables in the from clause. Then test and bang it was done! I did alot of these in less that five minutes.
I haven't systematically gone through intranet yet to
see if I can do similar simplifications (I was originally motivated by the fact that a couple of your big unions didn't quite work right, but I suspect most of them do).
That might be a could thing to do for all of the modules once they stabilize. I didn't want to spend too much time on intranet as my impression was that it was still a work in progress from aD. Major changes occured from 3.1 to 3.2, and there still seem to be some design issues that could be fixed. Have you looked at intranet in the 3.2.2 release? There might be some new fixes there.
