Skip site navigation (1) Skip section navigation (2)

SQL query not working when GROUP BY / HAVING is used

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 (view raw or flat)
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
============================================


Responses

pgsql-sql by date

Next:From: Bruno BoettcherDate: 2000-12-19 12:37:52
Subject: replace??
Previous:From: Tulassay ZsoltDate: 2000-12-19 09:21:18
Subject: Re: Tree structure table normalization problem (do I need a trigger?)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group