[thelist] Owed tip - SQL Decode as a quick fix for data quality
Luther, Ron
Ron.Luther at hp.com
Tue Oct 4 15:49:48 CDT 2005
Hi Gang,
Sorry. IGT. [1]
<Shrug /> It happens.
<tip type = "SQL Decode as quick fix for data quality" author = "RonL.">
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.]
Now the app is running again and I have more time to consider how to
best cause pain to the folks responsible!
</tip>
HTH,
RonL.
[1] I Got Trolled.
More information about the thelist
mailing list