Re: SQL query not working when GROUP BY / HAVING is used

I hope it may help:
1. if you use group clause in a select stmt, the select list must be
agregate function such as sum(field),count(field), max(field)...,
cannot use field.
2. for field have NULL field, should use field IS NULL, = NULL will give
you wrong result!


Jie LIANG

Internet Products Inc.

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang@ipinc.com
www.ipinc.com

On Tue, 19 Dec 2000 juerg.rietmann@pup.ch wrote:

> Hello there
> 
> I have a question regarding a SQL statement.
> 
> When I execute (and that's what I need)
> 
> SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, Zylinder.Z_durch_ist,
> ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) AS durchmesserdelta,
> (Zylinder.Z_durch_soll+0.12) AS effektiv
> FROM Auftrag,Zylinder_Typen, Zylinder
> WHERE Auftrag.A_nr = '11'
> AND (  Auftrag.A_farbe_1  > '0' AND Zylinder_Typen.Z_farbe='1' AND
> Zylinder_Typen.Z_SW='0')
> AND Zylinder_Typen.Z_durch_soll = Zylinder.Z_durch_soll
> AND Auftrag.A_Ztyp=Zylinder.Z_typ
> AND Z_A_nr = NULL
> AND Z_status = 'zcu'
> GROUP BY Zylinder.Z_durch_soll
> HAVING durchmesserdelta >= 0.085
> ORDER BY Zylinder_Typen.Z_durch_soll desc
> 
> I get the following error in the pgadmin.log file.
> 
> 19.12.00 10:53:34   Executing: SELECT Zylinder.Z_durch_soll, Zylinder.Z_id,
> Zylinder.Z_durch_ist, ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist)
> AS durchmesserdelta, (Zylinder.Z_durch_soll+0.12) AS effektiv FROM
> Auftrag,Zylinder_Typen, Zylinder WHERE Auftrag.A_nr = '11' AND (
> Auftrag.A_farbe_1 > '0' AND Zylinder_Typen.Z_farbe='1' AND
> Zylinder_Typen.Z_SW='0') AND Zylinder_Typen.Z_durch_soll =
> Zylinder.Z_durch_soll AND Auftrag.A_Ztyp=Zylinder.Z_typ AND Z_A_nr = NULL
> AND Z_status = 'zcu' GROUP BY Zylinder.Z_durch_soll HAVING durchmesserdelta
> >= 0.085 ORDER BY Zylinder_Typen.Z_durch_soll desc
> 19.12.00 10:53:34   Executing SQL Query...
> 19.12.00 10:53:34   Done - 0,01 Secs.
> **********************************************************************
> * Error - 19.12.00 10:53:34
> **********************************************************************
> 
> Software
> ********
> Program: pgAdmin
> Version: 7.0.4
> Sub or Function: frmSQL, cmdExecute_Click
> 
> Error Details
> *************
> Error No: -2147217887
> Error Description: Der ODBC-Treiber unterst�tzt die angeforderten
> Eigenschaften nicht.
> Error Source: Microsoft OLE DB Provider for ODBC Drivers
> DLL Error Code: 0
> 
> Memory Details
> **************
> Total Physical: 132435968
> Total Swap: 434098176
> Total Virtual: 2147352576
> Available Physical: 34004992
> Available Swap: 291512320
> Available Virtual: 2079350784
> Percentage Free: 0
> 
> System Details
> **************
> Processor: 586
> OEM ID: 0
> No. Processors: 1
> Page Size: 4096
> 
> OS Details
> **********
> Platform: Windows NT
> Version: 4.0
> Build: 1381
> OS Info: Service Pack 5
> 
> Environment Details
> *******************
> Datasource: pgmondadori
> Tracking: False
> TrackVer: 0
> Connect: Provider=MSDASQL.1;Extended Properties
> ="DSN=pgmondadori;DATABASE=mondadori;SERVER=srvlnx01.pup.ch;PORT=5432;UID=postgres;PWD
> 
> ******;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=1;SHOWOIDCOLUMN=1;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS
> 
> "
> Version: 2.6
> 
> 
> Using the same statement without the GROUP BY and HAVING it is ok !
> 
> SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, Zylinder.Z_durch_ist,
> ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) AS durchmesserdelta,
> (Zylinder.Z_durch_soll+0.12) AS effektiv
> FROM   Auftrag,Zylinder_Typen, Zylinder
> WHERE   Auftrag.A_nr = '11'
> AND (Auftrag.A_farbe_1  > '0' AND Zylinder_Typen.Z_farbe='1' AND
> Zylinder_Typen.Z_SW='0')
> AND  Zylinder_Typen.Z_durch_soll = Zylinder.Z_durch_soll
> AND    Auftrag.A_Ztyp=Zylinder.Z_typ
> AND    Z_A_nr =NULL
> AND    Z_status = 'zcu'
> ORDER BY Zylinder_Typen.Z_durch_soll desc
> 
> Whats wrong, according to the docs, the syntax is ok and it should be
> possible to use these keywords!
> 
> ============================================
> PFISTER + PARTNER, SYSTEM - ENGINEERING AG
> Juerg Rietmann
> Grundstrasse 22a
> 6343 Rotkreuz
> Switzerland
> 
> phone: +4141 790 4040
> fax: +4141 790 2545
> mobile: +4179 211 0315
> ============================================
>