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(at)ipinc(dot)com
www.ipinc.com

On Tue, 19 Dec 2000 juerg(dot)rietmann(at)pup(dot)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 untersttzt 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
> ============================================
>