DSXchange

Hi all,

Im using InfmxCli and i cant execute a query like that:

SELECT a, MAX(b) FROM tabA
GROUP BY a
HAVING MAX(b) = SELECT c FROM tabC

It gives the follow message:

[Informix] [Odbc Informix Driver] General error. Syntax error,
SQLExecute: Failed to execute statement.

If i use:

SELECT a, MAX(b) FROM tabA
GROUP BY a
HAVING MAX(b) = 1

it works, but when i put a select instruction it gives an error.

Anyone can help me?


Thanks in advance, Nuno Pimenta

Dont know about Datastage, but the syntax I use for this query in Informix
is:

SELECT a, MAX(b)
FROM tabA
GROUP BY a
HAVING MAX(b) in (SELECT c FROM tabC )


You cannot use = because "select c from tabc" can return more than one row

Hope this helps
Dirk

-----Original Message-----
From: Nuno Pimenta [mailto:nuno.pimenta@tmn.pt]
Sent: Monday, October 15, 2001 12:31 PM
To: LIST-DataStage (E-mail)
Subject: Query with HAVING


Hi all,

Im using InfmxCli and i cant execute a query like that:

SELECT a, MAX(b) FROM tabA
GROUP BY a
HAVING MAX(b) = SELECT c FROM tabC

It gives the follow message:

[Informix] [Odbc Informix Driver] General error. Syntax error,
SQLExecute: Failed to execute statement.

If i use:

SELECT a, MAX(b) FROM tabA
GROUP BY a
HAVING MAX(b) = 1

it works, but when i put a select instruction it gives an error.

Anyone can help me?


Thanks in advance, Nuno Pimenta





==========================================================
This message contains information intended for the perusal, and/or use (if so stated), by the stated addressee(s) only. The information is confidential and privileged. If you are not an intended recipient, do not peruse, use, disseminate, distribute, copy or in any manner rely upon the information contained in this message (directly or indirectly). The sender and/or the entity represented by the sender shall not be held accountable in the event that this prohibition is disregarded. If you receive this message in error, notify the sender immediately by e-mail, fax or telephone representations contained in this message, whether express or implied, are those of the sender only, unless that sender expressly states them to be the views or representations of an entity or person, who shall be named by the sender and who the sender shall state to represent. No liability shall otherwise attach to any other entity or person. ==========================================================