Joe Martinez schrieb:

> Take the following query:
>
> select (saleprice-salecost-saletradein) as Profit
> from sales where saledate = '08/08/04'

If you use FB1.5 you can do:

select
(
COALESCE ( saleprice, 0 ) -
COALESCE ( salecost, 0 ) -
COALESCE ( -saletradein, 0 )
) as Profit
from sales where saledate = '08/08/04'

>
> The problem is that if saletradein is NULL, then the resulting Profit is
> also null. That's not what I want. If saletradein is null, I want it to
> assume zero for that field. For instance 100 - 25 - NULL = 75. I know
> that logically, that's not technically correct, but that's the effect that
> I want.

You should define columns like that as NOT NULL and / or use triggers to
set them to 0 when insering / updating.

This will save you a lot of trouble...

Luc.