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.
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
Your_Field – Coalesce(Your_Field, 0)
Handy for dates as well, but small syntax change needed
COALESCE(Your_Field,date ‘2011-10-10’) as Your_Field
Your_Field – Coalesce(Your_Field, date ‘2011-10-10’)
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.