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 2010-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.