The first query uses the same table in both the select and the
sub-select.
The second working query uses the same view in both the select and the
sub-select.

How is this different?

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Arno Brinkman
Sent: Friday, January 27, 2006 5:32 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Similar SQL query fails

Hi,

> Fails with 'Cannot use an aggregate function in a WHERE clause, use
> HAVING instead'
>
> select
> d.PRODUCTID, (d.AWP / d.QTY) awp_ppu, d.GPI, d.PRODUCTID/100 ndc9
> from MDDB_DRUG d where d.GPI = ? and d.PRODUCTID/100 = ? and
> (d.AWP / d.QTY) <= ALL(
> select MIN( d.AWP / d.QTY )
> from MDDB_DRUG d2
> where d.PRODUCTID/100 = d2.PRODUCTID/100 and d.GPI = d2.GPI
> )
>
> If I remove the MIN it prepares without an error, but I don't know if
> it will return the same results (the column AWP is empty right now).

You can't use an aggregate in the where clause, so the message is
correct.

> This verion (different database, same version of Firebird) works, no
> error.
>
> select
> v1.NDC, (v1.PRICE / v1.TOT_QTY) awp_ppu, v1.GPI, v1.NDC/100 ndc9
> from V_MDDB_DRUGITEM_AWP v1 where v1.GPI = ? and v1.NDC/100 = ? and

> (v1.PRICE / v1.TOT_QTY) <= ALL(
> select MIN( v2.PRICE / v2.TOT_QTY )
> from V_MDDB_DRUGITEM_AWP v2
> where v2.NDC/100 = v1.NDC/100 and v2.GPI = v1.GPI
> )
>
> Note that it contains the MIN function, and that it references a view
> instead of a table.

Yes, but also the view from the sub-select. If you would use the table
d2 from the sub-select in the previous query then the MIN would be valid
too.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database development support:
http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features
:
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Firebird and Interbase users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info



++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on
the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links