[thelist] Owed tip - SQL Decode as a quick fix for data quality

[Ken Schaefer]

It may be helpful if you mention which DBMS this applies to.

I disagree with the use of the "dummy values" as well. Generally you'd throw
an exception when the data is entered -or- use a NULL (or similar).

Cheers
Ken

> -----Original Message-----
> From: thelist-bounces at lists.evolt.org [mailto:thelist-
> bounces at lists.evolt.org] On Behalf Of Luther, Ron
> Sent: Wednesday, 5 October 2005 6:50 AM
> To: thelist at lists.evolt.org
> Subject: [thelist] Owed tip - SQL Decode as a quick fix for data quality
> 
> 
> Sometimes 'required' data isn't.  Sometimes 'defined' formats aren't.
> 
> SQL Decode to the rescue!  ;-)
> 
> Last week I had an app break because some order entry folks decided that
> a single space would be a really cool value to enter into a date field.
> 
> 
> Fortunately it wasn't a field I was basing any calculations on ... so I
> haven't had to kill anybody over it ... well, not yet, anyway.
> 
> But it still fuxored the TODATE() function I was running in my SQL.
> 
> What to do?
> 
> The quick and practical fix was to add a DECODE to the function to pick
> off the single space value, which now reads as DECODE(' ', ' ',
> TODATE(yadda, yadda, yadda)). {or something like that.)
> 
> [Had this field actually been used in a critical calculation I could
> have used the above syntax to default in a 'trigger' value ... A valid
> date value, but an 'odd' value that would clue folks in to it's use as a
> derived placeholder.  This time I didn't need to do that, so I didn't.]
> 
> HTH,
> RonL.