Thursday, February 13, 2014

How to concat in Cognos report studio, blank / null problem Oracle


In certain reports, either data contains null values or the results of a complex query can return null values.
When performing a calculation, null values will cause the calculation to return a null value.
When concatenating names consisting of  surname, prefix and initials if one of the 3 is missing the result is a complete blank.

In most cases this may not be the desired result.

Only Oracle databases have this problem, other databases work fine.
IBM states its Oracles fault because Oracle does not do standard SQL.

One of the solutions is to change the data-items you want to concatenate so that a value is returned if the field is a blank. Here is an example:

[DATABASE].[EMPLOYEE].[PREFIX]


Change the above data-item into:
if
( [DATABASE].[EMPLOYEE].[PREFIX] is  NULL)
then
(  '  ' )
else
( [DATABASE].[EMPLOYEE].[PREFIX] )

Or change the above data-item into:
coalesce (  [DATABASE].[EMPLOYEE].[PREFIX] ; '  ' )



Do this with all the fields that may contain any blanks or null values in the whole Oracle database.
After this is done it is finally possible to concatenate the several database-fields in Oracle even if one field is NULL or blank.