Exercise #59 (tips and solutions)

Solution 2.2.1 doesn’t take into account the situation when a bay-back center received funds but didn’t make any payments, i.e. the Outcome_o table doesn’t contain any records related to the said center. In terms of the subject area. this could be the case for a freshly opened point that already registered the reception of funds to start operations, but didn’t collect any recyclables to pay for yet. For such a point, the expression in the SELECT clause

ss.inc - dd.out

will be equivalent to

ss.inc - NULL

yielding NULL instead of ss.inc, as it should be according to the task statement.

The solution can be corrected easily by rewriting the erroneous expression as

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

which corresponds to the SQL standard. Alternatively, the MS SQL Server ISNULL function could be used:

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

To return to discussion of exercise #59

To solve a problem on SQL-EX.RU