At 04:08 PM 31/01/2008, Yohanes Ongky Setiadji wrote:

>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.

You ARE getting the correct value. NULL is not a value, it is a state "unknown". So, if you subtract NULL from a value (or from another NULL) the result is unknown and returns NULL.

>Is there any function or statement in Firebird SQL so
>I can default NULL value to 0 (zero).

First, stop thinking about NULL as a value. ;-)

You can use an expression with COALESCE to enable a value to be used in place of null:

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

./heLen