Problems Concatenating Strings in Report Studio

[Morgan]

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!

[Morgan]

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.

[rockytopmark]

or you could have used the Coalesce() function (built-in to Cognos 8) that is designed to do just that.

[blom0344]

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.

[Lee Drake]

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 ||.

[Ty Clabbers]

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...