"select having" problem
Manuel A. Daponte Santiago got a syntax error adding HAVING count(unique ship_num) > 1 on IDS 7.31.UC2 under SCO. Rudy Fernandes offered a correlated subquery in HAVING, needing an index on ord_id. Jonathan Leffler suggested reversing it to HAVING 1 < COUNT(UNIQUE ...). Andreas Stern said that still gave error 201 on SE 7.13. Unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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