[thelist] Owed tip - SQL Decode as a quick fix for data quality
Ken Schaefer
Ken at adOpenStatic.com
Wed Oct 5 14:17:34 CDT 2005
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.
More information about the thelist
mailing list