Re: sql question

In article <1992Sep17.182003.15396_at_informix.com> cortesi_at_informix.com writes:
>In article <9539_at_emory.mathcs.emory.edu> baskett_at_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 Received on Sat Sep 19 1992 - 04:57:19 CEST