Subject Re: [firebird-support] putting zero value on null field in select sql
Author Daniel Rail

At December 18, 2003, 01:38, James wrote:

> I have a sql like this

> select m.ID, m."Expense", m."SubExpense", (d."TotalAmount" *
> (1-(d."Discount"*0.01)) * (1-(d."Discount2"*0.01)) *
> (1-(d."Discount3"*0.01)) - d."Less" - d."Adjustment") as "GrandTotal"
> from "expensesubtype" m left join "invoice" d on m.ID = d."ExpenseCategory"

> What happen is if I have no record in the invoice table that mathes in
> the expensesubtype the "GrandTotal" is null. How do I replace the null
> with zero? Iam trying to avoid using SP

It's not possible with FB 1.0, other than using an UDF.
In FB 1.5, there's a new DSQL function called COALESCE that does what
you want(read the release notes for more information).

Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (
ACCRA Med Software Inc. (