Русский    English

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

  1. ss.inc - dd.out
for such a reception point will be equivalent to

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

  1. (COALESCE (ss.inc, 0) - COALESCE (dd.out, 0) )
which corresponds to the standard, or using the SQL Server ISNULL function:

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

To return to discussion of exercise #59

To solve a problem on SQL-EX.RU

Bookmark and Share
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.