Re: sql question

In article <9539@emory.mathcs.emory.edu> baskett@hermes1.sps.mot.com (Bob Baskett) writes: > > help, im having trouble doing a select. we have a table (trace) which > has two trace_types, (WAF and ASM). what i want to know is the ASM > source_lots which have more than one WAF source_lot. > > the trace table: > > create table trace > ( > source_lot char(16), > test_lot char(16), > test_lot_class char(3), > ... > );> > 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.