Hi,

I have 3 tables:

A. MASTER
B. PURCHASING TRANSACTION DETAIL
C. RECEIVING TRANSACTION DETAIL

ex.

SELECT A.code,
B.purchased,
C.received,
B.purchased - C.received AS remain
FROM A
LEFT JOIN B
ON B.code = A.code
LEFT JOIN C
ON C.code = A.code

the result:

A.code B.purchased C.received remain
------ ----------- ---------- ------
000001 100 50 50
000002 50 NULL NULL

with LEFT JOIN, I can get all master table data from
table A and the transaction from table B and C.
But when there is no receiving transaction in table C
the received field return NULL value.

My question is I can get the correct value in field
'remain' if the C.received is NULL.
Is there any function or statement in Firebird SQL so
I can default NULL value to 0 (zero).

Regards,

Ongky.


Send instant messages to your online friends http://uk.messenger.yahoo.com