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