loading..
Русский    English
23:55
go

Exercise #60 (tips and solutions)

In 2.3.1 decision it is used full external join (FULL JOIN) of subqueries, to consider all possible cases: when income sum or outcome sum is NULL for some reception point in result of execution these subqueries (in other words, there was no income or outcome). If, to example, income is 1000 and outcome is 800, then all possible cases will be considered:

Income    Outcome
1000    800
NULL    800
1000    NULL

Case NULL NULL cannot be, because it would mean that the reception point did not exist (at this point in time).

In SELECT clause construction is used to replace the NULL by zero in clause of calculating the balance. Logic absolutely correct, but the design is applied incorrectly:

  1. CASE inc 
  2.      WHEN NULL 
  3.      THEN 0 
  4.      ELSE inc 
  5. END

Mistake is that there actually involved a simple comparison operation with a NULL value, namely,

  1. CASE 
  2.      WHEN inc = NULL 
  3.      THEN 0 
  4.      ELSE inc 
  5. END

Comparing with the NULL-value always yields UNKNOWN. Therefore WHEN condition is not fulfilled, resulting in running a ELSE branch, always returning the inc value, even in the case where inc is NULL.

To return to discussion of exercise #60

To solve a problem on SQL-EX.RU
Bookmark and Share
Tags
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.