"select having" problem

"select having" problem

Post by Manuel A. Daponte Santiag » Sun, 31 Dec 1899 09:00:00



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

 
 
 

"select having" problem

Post by Rudy Fernande » Sun, 31 Dec 1899 09:00:00


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


Quote:> ....
> 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


 
 
 

"select having" problem

Post by Jonathan Leffle » Sun, 31 Dec 1899 09:00:00



Quote:> 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.

--

Guardian of DBD::Informix v0.95 -- see http://www.perl.com/CPAN
#include <disclaimer.h>

 
 
 

"select having" problem

Post by Andreas Ster » Sun, 31 Dec 1899 09:00:00




> > 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

 
 
 

"select having" problem

Post by Richard harnde » Sun, 31 Dec 1899 09:00:00


This is no help to you but ...

select  tabid, count(*)
-- or select tabid, count(colno)
from    syscolumns                              
where   tabid < 1000                            
group by        tabid                                  
having  count(distinct colno) > 1

works just fine.

select  tabid, count(distinct colno)
...
having count(distinct colno) > 1

gives a syntax error.

It doesn't seem to like count(distinct xxx) in the select and having clauses.
Got absolutely no idea why.

I think that you're gonna have to use a temp table.




> > 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

 
 
 

"select having" problem

Post by Andreas Ster » Sun, 31 Dec 1899 09:00:00


hi,

I just tried successfully with a subquery in the condition, like

SELECT count(unique eb_code)ebeanzahl,ku_code, fa_code
from ebeschwerde, vangebot
WHERE
eb_erfass>="1999-11-01 00:00"
AND vangebot.ob_code=ebeschwerde.ob_code
AND vangebot.ku_nr=ebeschwerde.ku_code
--...lot of stuff omitted
AND ( (select count(unique eb_code)
   from ebeschwerde e
   where e.ku_code=ebeschwerde.ku_code) >2)
GROUP by ku_code, fa_code

I should check, if it does, what I inted it to do!

thanks a lot!
A.


> This is no help to you but ...

> select     tabid, count(*)
> -- or select tabid, count(colno)
> from       syscolumns                              
> where      tabid < 1000                            
> group by   tabid                                  
> having  count(distinct colno) > 1

> works just fine.

> select     tabid, count(distinct colno)
> ...
> having count(distinct colno) > 1

> gives a syntax error.

> It doesn't seem to like count(distinct xxx) in the select and having clauses.
> Got absolutely no idea why.

> I think that you're gonna have to use a temp table.




> > > 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.

 
 
 

"select having" problem

Post by Mark D. Stoc » Sun, 31 Dec 1899 09:00:00





> > > 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)

I have the _distinct_ impression that COUNT(UNIQUE colname) was
introduced in version 7.20 or some such version. But then my impressions
are usually UNIQUE. :-O

If you can upgrade to the latest version, then it would be advisable,
for many reasons. Year 2000 for one, which should be upon us soon. ;-)

Cheers,
--
Mark.

+----------------------------------------------------------+-----------+

| http://www.informix.com  http://www.informixhandbook.com |/////  / //|
| http://www.iiug.org  +-----------------------------------+////  / ///|
|                      |What year 2000 bug? year 2000 bug? |///  / ////|
|                      |year 2000 bug? year 2000 bug? year |//  / /////|
|                      |2000 bug? year 2000 bug? year 1900 |/  ////////|
+----------------------+-----------------------------------+-----------+

 
 
 

1. Re(4): "select having" problem

That's alright sweetie pie.

I've never been arrested for it Dude!

Not really. Here are the options:

select ord_id, count(*)

which groups by ord_id then counts the number of rows in each group.

select ord_id, count(ship_num)

which groups by ord_id then counts the number of ship_num's in each group. So this is the same as count(*).

select ord_id, count(unique ship_num)

which groups by ord_id then counts the number of unique ship_num's in each group. So if you have two of the same ship_num's in the same group, it will only get counted once.

Try these selects in any database:

select colno, count(*)
from syscolumns
group by 1
order by 1
;
select colno, count(collength)
from syscolumns
group by 1
order by 1
;
select colno, count(unique collength)
from syscolumns
group by 1
order by 1

Have another beer Dude, you have exhausted my brain cell now. Happy BDay!

AB
----------------------------------------------------------------
Get your free email from AltaVista at http://altavista.iname.com

2. create large index

3. Re(2): "select having" problem

4. seq. scans

5. Having "HAVING" problems with GROUP BY.

6. So Cal User Group Meeting

7. Having problems with "Insert Into" command

8. Help with multiple columns using DataCombo and DataList

9. max of ("...","...","..")

10. "Select * from Insert Into" Locking Problem

11. Select MIN() "problem"

12. "Select Into #Iemp" problem

13. Problem with "Join"-ing in a select statement