Exercise #59 (tips and solutions)

The 2.2.1 decision ignores the fact that it might happen that at some point only income may be, but outcome not to be, i.e. it is possible that Outcome_o table does not have any rows related to the this point. In terms of subject area, this situation is possible to new reception point, when the fact of cash income already recorded, but the fact of cash outcome is not. Then the expression in the SELECT clause

ss.inc - dd.out

for such a reception point will be equivalent to

ss.inc - NULL

that will result in NULL, and not ss.inc, as it should be under the terms of the problem.

Correct solution is very easy by rewriting the erroneous expression in the form:

(COALESCE (ss.inc, 0) - COALESCE (dd.out, 0) )

which corresponds to the standard, or using the SQL Server ISNULL function:

(ISNULL(ss.inc, 0) - ISNULL(dd.out, 0) )

To return to discussion of exercise #59

To solve a problem on SQL-EX.RU