NULLs are one of the more controversial things that a DB2
professional has to deal with. And I do mean HAS to deal with.
Because of all the controversy about their usefulness, implementation, and
confusing nature, some DB2 folks just decide to hide their head in the sand and
ignore NULLs. The thinking goes something like this: “If I do not create any
nullable columns in any of my DB2 tables, then I can blissfully ignore the
whole NULL mess and be happy!”
Well, that is simply not true. You can follow this approach and still
write a query that will return NULL. Don’t believe it? Run this query then:
SELECT AVG(PRSTAFF)
FROM
DSN8B10.PROJ
WHERE
DEPTNO = ˈXOXˈ;
PRSTAFF is defined as a DECIMAL(5,2) column and we are
trying to find the average staffing for a specific department. The PRSTAFF
column can contain nulls, but even if it could not, the result here would be
the same. This query returns a NULL (unless someone inserted a row with the
value of ‘XOX’ for DEPTNO at your site).
Why? There is no department ‘XOX’ in the sample databases (provided with DB2). So, the query is attempting
to find an average for the empty set. This is NULL. Some folks think this query should return 0, but it won't! The sum of all PRSTAFF for
the department ‘XOX’ is not zero, but is unknown…
So, take the time to understand how NULLs work in DB2, because they cannot be ignored!
No comments:
Post a Comment