"select having" problem

Hi everybody ! This select works fine: select ord_id, count(unique ship_num) from ord_l where ord_id < 1000 group by ord_id But when I try to further filter the data with a having clause all I get is a syntax error select ord_id, count(unique ship_num) from ord_l where ord_id < 1000 group by ord_id having count(unique ship_num) > 1 I have Informix Dynamic Server Version 7.31.UC2 on a SCO Release = 3.2v5.0.4 Thanks in advance !!! -- Manuel A. Daponte Santiago Systems Consultant, ICP

You could do the following : select ord_id, count(unique ship_num) from ord_l a where ord_id < 1000 group by ord_id having ( (select count(unique ship_num) from ord_l b where a.ord_id = b.ord_id) > 1); If there's no index on ord_l(ord_id), you're toast. Otherwise, it should work reasonably well. Rudy "Manuel A. Daponte Santiago" wrote: > .... > select ord_id, count(unique ship_num) > from ord_l > where ord_id < 1000 > group by ord_id > having count(unique ship_num) > 1> > I have Informix Dynamic Server Version 7.31.UC2 on a SCO Release = 3.2v5.0.4 > > Thanks in advance !!! > > -- > Manuel A. Daponte Santiago > Systems Consultant, ICP

"Manuel A. Daponte Santiago" wrote: > Hi everybody ! > > This select works fine: > > select ord_id, count(unique ship_num) > from ord_l > where ord_id < 1000 > group by ord_id> > But when I try to further filter the data with a having clause all I get is > a syntax error > > select ord_id, count(unique ship_num) > from ord_l > where ord_id < 1000 > group by ord_id > having count(unique ship_num) > 1> > I have Informix Dynamic Server Version 7.31.UC2 on a SCO Release = 3.2v5.0.4 Try: HAVING 1 < COUNT(UNIQUE Ship_Num) If it works, then it is because the SQL standard is unbelievably obtuse and insists on having things just so, and because Informix is equally unbelievably obtuse enough to implement exactly what the standard says and not apply one iota of common sense to the grammars it accepts. I find it hard to believe that any conformance test could fail a product because it accepted a query which the standard doesn't support, but that would be just about the only excuse. If the reversed HAVING clause doesn't work either, then I'm not clear what's up. It might be to do with a repeated UNIQUE, but you said "syntax error" which I take to be error -201. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN #include <disclaimer.h>

Jonathan Leffler wrote: > > "Manuel A. Daponte Santiago" wrote: > > > Hi everybody ! > > > > This select works fine: > > > > select ord_id, count(unique ship_num) > > from ord_l > > where ord_id < 1000 > > group by ord_id> > > > But when I try to further filter the data with a having clause all I get is > > a syntax error > > > > select ord_id, count(unique ship_num) > > from ord_l > > where ord_id < 1000 > > group by ord_id > > having count(unique ship_num) > 1> > > > I have Informix Dynamic Server Version 7.31.UC2 on a SCO Release = 3.2v5.0.4 > > Try: > > HAVING 1 < COUNT(UNIQUE Ship_Num) > sorry to tell you, that even that gives # ^ # 201: A syntax error has occurred. # (SE 7.13 UC1 on an HP 10.01) A. -- Andreas Stern EDV Entwicklung Fon: +43 1 / 402 33 00 19 AS Dienstleistungen Fax: +43 1 / 402 33 00 27