I have 6 fields that I want to concatenate. Sometimes only 3 of the string fields are populated, and sometimes all 6 string fields are populated. I used the || to concatenate the 6 fields, but am only getting results when all 6 string fields are populated.
Also worthy of note: I got a crazy 5k line SQL code for the concatenate statement from Report Studio when I took the SQL and put it in TOAD. When I took out the the 5k lines and put in a simple || statement, I got good results.
Thank you!
I figured this out.
First, this is a so-called "feature" of Cognos according to the Report Studio User's Guide, page 225:
When Cognos 8 concatenates strings locally and if any of the involved strings contain null values, the result of the concatenation is an empty cell or a null value. This occurs because Cognos 8 requires that an expression that involves a null value returns a null value. Many databases ignore null strings when they perform concatenations. For example, if you concatenate strings A, B, and C, and if string B is a null value, the database may concatenate only strings A and C.
To work around this, I built six query calculations, and named them "Modified_String ", "Modified_String2" etc. They are coded as follows:
if ([String 1] is null)
then (' ')
else ([String 1])
This puts a value into the string, even though it's only a space. The strings are now no longer null.
I then concatenated the "Modified_String1", "Modified_String2" etc. as follows:
[Modified_String1]||' '||
[Modified_String2]||' '||
[Modified_String3]||' '||
[Modified_String4]||' '||
[Modified_String5]||' '||
[Modified_String6]
Viola.
or you could have used the Coalesce() function (built-in to Cognos 8) that is designed to do just that.
Be prepared to have the same experience with calculations.. What works with TOAD in Oracle may need some tweaking when it is processed by Cognos.
I encountered this recently. I solved it by using nested concat functions. Since concat is an Oracle function, Cognos does not rewrite the statement like it does when it encounters the ||.
A Cognos consultant once told me they use ansi sql and oracle doesn't. I am trying to write this without any sarcastic thoughts. And fail...