smurftech

Teradata – Using a Date field and Coalesce

This is a simple one, but had me stumped for awhile.

For those of you who haven’t used coalesce, it’s a handy bit of code which allows you to replace a NULL value with what ever you wish to use.

I have used Coalesce plenty of time before, but normally for an integer, today I tried to use it for a date value and then spent the next 15min trying to figure out why it would work.

Normal syntax

COALESCE(Your_Field,0) as Your_Field

The above code is fairly simple, when ever Your_Field contains a NULL value, it will be replaced with 0, if it isn’t NULL it keeps it’s value

E.g.
Your_Field – Coalesce(Your_Field, 0)
1 1
a a
3 3
NULL 0
0 0

Pretty simple,

Handy for dates as well, but small syntax change needed

COALESCE(Your_Field,date ‘2011-10-10’) as Your_Field

E.g.
Your_Field – Coalesce(Your_Field, date ‘2011-10-10’)
2010-01-01 2010-01-01
2009-11-01 2009-11-01
NULL 2011-10-10
1996-07-18 1996-07-18

So as with the previous example any NULL value is replaced. For some reason you have to qualify that you are using a date.

Anyway thought this may save someone else stumbling through trying to find the correct syntax so hope it helps.

Cheers Smurf

Leave a Reply