10.4. Calendar Scrubs
When a range of possible values is limited, you can use a table for those Values that you wish to allow into the database schema.
The idea is simple enough and should have been part of the CHECK () constraints on the base tables in the schema. But when you are importing external data, you might need help.
Troels Arvin posted a problem in the DB2 newsgroup in early 2007 in which he had a non-SQL data source with CHAR (10 ) dates that were supposed to be in ISO-8601 format (i.e., 2006-12-24). Some of the values were known to be invalid (such as 0000-00-00 or 2006-02-45). His goal was to convert the strings to DATE values as he loaded them into his schema.
One proposed solution was to use an internal use-defined function (UDF) ...
Get Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
