Report Studio
Coalesce Or How to Extract Non-Null Values
(Dawn Yost) In Cognos, null values in returned query/report rows can be frustrating to work with. Null values can cause errors or inconsistent report results under the following situations: the SQL concatenation command ( '||' ) does not return null values. You won't receive an error message. Instead, Cognos will stop returning column values when it encounters the first null; therefore you will drop values from the report. arithmetic calculations....we all know you cannot divide by zero...you also cannot divide by null. To prevent errors, you can use the expression builder to construct a DECODE, IF, or CASE statement to handle the null. However, this method can prove too laborious under the following situations: attempting to concatenate more than 3 fields (requires excessive DECODE statements) complex arithmetic (where the additional DECODE to handle the nulls, turns the math formula into an overly complex formula.) The answer to your prayers......ta da....the Cognos Coalesce Function. COALESCE is a built-in Cognos common function that returns the first nonnull item in a list. It's handy to return items in a preferred sequence of "nonnullness", ultimately returning something nonnull at the end so other functions that don't handle nulls well have something to work with. EXAMPLE 1: Concatenating multiple fields that may contain nulls using the coalesce function:
coalesce([Schedule coalesce([Schedule coalesce([Schedule coalesce([Schedule coalesce([Schedule Offering].[Meeting Offering].[Meeting Offering].[Meeting Offering].[Meeting Offering].[Meeting Time].[MONDAY_IND],'')+ Time].[TUESDAY_IND],'')+ Time].[WEDNESDAY_IND],'')+ Time].[THURSDAY_IND],'')+ Time].[FRIDAY_IND],'')
Explanation: The Coalesce function will interpret each column and return only those columns that do not have nulls. Therefore, the above statement will return M W when only these 2 columns contain value. If you tried to concatenate the same data, only M would be returned (because there was no Tuesday). Nasty part of it... you may not know its happening.
9/22/2010
Page 1
Report Studio
Example report is out on the Student folder: Dawn's Sample Coalesce Function Important Note - my report shows all of the above columns AND the Coalesce Function AND what happens when you try to use Concatenation....results are shocking !
Example Results:
COURSE_NUMBER MONDAY_IND TUESDAY_IND WEDNESDAY_IND THURSDAY_IND FRIDAY_IND SATURDAY_IND SUNDAY_IND Days Calc
8566 9615 9982 9994 9998 9999 0829 0829 0829 0829 0829 M M M M M
T R
T R
T T T
W W W
R R
MTWRFS U MTWR MT
M WF M
EXAMPLE 2: Using the coalesce function in calculations in place of DECODE, IF, or CASE statements: In this example, consider the data shown below: Product Red Balls Blue Balls Green Balls Yellow Balls Sale Price 2.44 4.88 Regular Price 8.99 7.23 6.99 Quantity 14 0 12 18
Certain items contain nulls. For example, some items were not on sale when sold and therefore Sale_Price has no value. One item, Blue Balls, has no value for either Sale_Price or Regular_Price since it was a promotional item given away for free. If the report requires a Revenue calculation that multiplies the actual cost of the item at the point of sale with the quantity sold, the calculation might be as follows:
(if([Sale_Price] is null and [Regular_Price] is null) then (0) else (if([Sale_Price] is null) then ([Regular_Price]) else ([Sale_Price]))) * [Quantity]
9/22/2010
Page 2
Report Studio
However, as a simpler syntax alternative, consider using the coalesce function as shown below:
coalesce([Sale_Price], [Regular_Price], 0) * [Quantity]
The coalesce function returns the first non null value. In the expression above, if Sale_Price is not null, it will be returned. If Sale_Price is null, it will check Regular_Price. If Regular_Price is not null, it will be returned. If Regular_Price is null, it will return the static value of 0
9/22/2010
Page 3
