Subject Re: [firebird-support] Force NULL to zero in calculation?
Author Martijn Tonies

> Take the following query:
> select (saleprice-salecost-saletradein) 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.
> Is there any way to write the query so that it works that way?

With Firebird 1.5, you can use COALESCE.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Upscene Productions