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
No comments:
Post a Comment