Re: sql question

In article <1992Sep17.182003.15396@informix.com> cortesi@informix.com writes: >In article <9539@emory.mathcs.emory.edu> baskett@hermes1.sps.mot.com (Bob >Baskett) writes: >> my first select looks like: >> select a.source_lot, count(DISTINCT b.source_lot) >> from system:trace a, system:trace b >> where a.trace_type = "ASM" >> and b.trace_type = "WAF" >> and b.test_lot = a.test_lot >> and b.test_lot_class = a.test_lot_class >> group by a.source_lot >> having count(DISTINCT b.source_lot) > 1>> >> which yeilds a syntax error on the 'having count(DISTINCT...'. > >This question created a lot of discussion around here. The >authoritative reply is that you cannot use DISTINCT twice >in a single SELECT. That's an ANSI SQL rule, and the basis >of the syntax error. Looks like you'll have to use a temp >table. Alternatively of course you could use esql or 4GL >and apply the logical equivalent of HAVING in procedural code. >Or possibly you could achieve this effect in an AFTER GROUP >clause of an ACE or (more likely) 4GL report. The apparent problem here in ANSI is the overloading of the use of the word DISTINCT in both query/subquery definitions and in aggregate functions. There is really no particular conflict to using DISTINCT multiple times in aggregate functions. Since each <distinct set function> has as its argument a different set of values for each grouped row, it is completely possible (albeit a bit costly in terms of processing) to eliminate redundant values in each list. There is clearly a problem using DISTINCT in a query/subquery specification multiple times. More importantly, when DISTINCT is used in a HAVING solely for the purpose of referencing a column which has already been created for inclusion in the SELECT list and it is the only column that has DISTINCT in it, there is simply no justification for complying with this ANSI restriction (except solely to be dogmatic about ANSI). The columns are the same and should be treated that way by the DBMS. This would be about as silly as saying you can't use the AVG function on the same column in both the SELECT list and the HAVING clause which would pretty much doom the usefulness of the HAVING clause. Personally, I think this is a glitch by the ANSI committee for the 89 spec (SQL2 clearly does not have this limitation as far as I can tell with the generalization of subqueries). Anyone out there on the ANSI committee willing to try and justify why this restriction is in there? Jon Rosen