Hi Dixon,

> in the following statement:
> (SUM(d.SD_TOTAL) - SUM(d.SD_QTY*p.PR_PRICE)- SUM(d.SD_QTY * (SELECT
> x.TX_AMTEA FROM PRTAXCODE x
> WHERE (x.TX_CODE=d.SD_TAXCODE AND x.TX_CAT=p.PR_CAT)))) AS PRODDIF
>
> x.TX_CAT and x.TX_CODE and x.TX_AMTEA may or may not be NULL.
>
> If it is null, I would like to return a value of zero for the last
> subSelect so that the value of this field would be the difference of
> (SUM(d.SD_TOTAL) - SUM(d.SD_QTY*p.PR_PRICE) AS PRODDIF
>
> is this possible to convert a NULL to a zero on the fly?

In Firebird 1.5, use COALESCE.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com