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

[Luther, Ron]

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.