Обсуждение: SQL query not working when GROUP BY / HAVING is used
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 ============================================
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 This query is not legal SQL. All columns in the select list of a group by query must either be grouped columns or set value functions (pretty much anyway). The general construct is legal but there are syntax rules for GROUP BY that you are violating. If Z_id and Z_durch_ist are unique for values of Z_durch_soll you might try grouping on all of them. If not, the query above is indeterminate since you are not specifying which Z_id and which Z_durch_ist to use for a particular Z_durc_soll value.
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 > ============================================ >
Hope it helps: 1. If you use GROUP, the select list should sum|count|max ..., no single field. 2. If you use NULL, the condition should be field IS [NOT] NULL, = NULL will give the wrong answer. 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 > ============================================ >