SQL COALESCE

SQL Reporting Services is a great tool. It allows you to create flexible and exportable (to pdf etc) reports. However, things sometimes get a bit complicated and a normal problem that you face is the problem of columns containing null-values that you want to use for calculations. For this there is a nice function called COALESCE which replaces null with the value directed.

For example:

SELECT firstname, lastname, age FROM contacts;

Might return the list:

firstname        lastname        age

John            Smith            5

Peter            Jacks            null

If you want to use the age-column to do some calculations or you just don’t want it to be null, just change your SELECT-statement to:

SELECT firstname, lastname, COALESCE(age, 0) As NewAge, COALESCE(age, 0) * 2 As DoubleAge FROM contacts;

This will give you the result:

firstname        lastname        NewAge    DoubleAge

John            Smith            5        10    

Peter            Jacks            0        0

 

A useful and simple functions, to get you out of some nasty trouble when doing for instance LEFT OUTER JOIN, RIGHT OUTER JOIN or FULL OUTER JOIN when the risk of returning null is great.

Gustaf

Humandata AB