From: | juerg(dot)rietmann(at)pup(dot)ch |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | SQL query not working when GROUP BY / HAVING is used |
Date: | 2000-12-19 09:59:17 |
Message-ID: | OF208AB5D1.6DAC762C-ONC12569BA.00365A4B@pup.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
============================================
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Boettcher | 2000-12-19 12:37:52 | replace?? |
Previous Message | Tulassay Zsolt | 2000-12-19 09:21:18 | Re: Tree structure table normalization problem (do I need a trigger?) |