From: juerg.rietmann@pup.ch
To: pgsql-sql@postgresql.org
Subject: SQL query not working when GROUP BY / HAVING is used
Date: Tue, 19 Dec 2000 10:59:17 +0100
Message-ID: <OF208AB5D1.6DAC762C-ONC12569BA.00365A4B@pup.ch> (raw)
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
============================================
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: juerg.rietmann@pup.ch
Subject: Re: SQL query not working when GROUP BY / HAVING is used
In-Reply-To: <OF208AB5D1.6DAC762C-ONC12569BA.00365A4B@pup.ch>
References: <OF208AB5D1.6DAC762C-ONC12569BA.00365A4B@pup.ch>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox