I have a query like this:

SELECT SUM(INVOICE.TOT_OWING)
FROM INVOICE
WHERE INVOICE.FK_ACCOUNT_ID = :A_ACCOUNT_ID AND
(((:V_CURRENT_DATE - INVOICE.INVOICE_DATE) >=31) AND ((CAST('NOW' AS DATE) - INVOICE.INVOICE_DATE) <=60)) AND
INVOICE.IS_PROCESSED = 'Y'
INTO :V_30_INV_TOTAL;

It works great when there is data in the range that is being searched. But when there is no data in the range, rather than returning a 0 (being the sum of nothing), its returning NULL.

Unfortunately this query (and others like it) are run in a larger stored procedure, and the results are there added together, which is causing a problem trying to add anything to NULL.

So I need to force a Decimal(15,2) return type from this, but I have no idea how that should be done?

Myles

P.S. This is with FB 1.5.5 Super server if that makes any difference.