Exercise #60 (tips and solutions)

Solution 2.3.1 uses a full outer join (FULL JOIN) of subqueries to consider all cases when either the received funds (inc) or the payments (out) for a point are NULL values. Say, if the received sum is 1000 and the paid one is 800, all possible combinations will be taken into account:

The case NULL NULL is not possible, since it would mean the respective point just didn’t exist (at that moment in time).

The SELECT clause uses a construct for replacing NULL by zero in the cash balance calculating formula. The logic is absolutely correct, but the implementation is flawed:

CASE inc
    WHEN NULL
    THEN 0
    ELSE inc
END

The mistake lies in actually using a direct comparison with a NULL value, namely

CASE
    WHEN inc = NULL
    THEN 0
    ELSE inc
END

Comparing with NULL always yields UNKNOWN. Therefore, the condition in WHEN is never met, resulting in the ELSE branch being executed in all cases and returning inc even if it’s NULL.

To return to discussion of exercise #60

To solve a problem on SQL-EX.RU