Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id eBJE3M682661 for ; Tue, 19 Dec 2000 09:03:22 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from srvlnx02.pup.ch (zg-d-187.agrinet.ch [212.28.131.187]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBJAEj670316 for ; Tue, 19 Dec 2000 05:14:46 -0500 (EST) (envelope-from juerg.rietmann@pup.ch) Subject: SQL query not working when GROUP BY / HAVING is used To: pgsql-sql@postgresql.org X-Mailer: Lotus Notes Release 5.0.5 September 22, 2000 Message-ID: From: juerg.rietmann@pup.ch Date: Tue, 19 Dec 2000 10:59:17 +0100 X-MIMETrack: Serialize by Router on srvlnx02/Pup/ch(Release 5.0.5 |September 22, 2000) at 12/19/2000 11:17:08 AM MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by mail.postgresql.org id eBJAEl670317 X-Archive-Number: 200012/214 X-Sequence-Number: 548 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 ============================================