Generated SQL: CASE WHEN check IS NULL

Cognos 8 Business Intelligence Framework Manager
1

I have a question on how to manupulate the generated sql from Framework Manager or Report Studio.

I have a table in Oracle 11g, call it DIM_DATE with columns SEASON and SEASONYEAR, both mandatory.
In a report the end user checks if SEASON||SEASONYEAR = 'Z10’
A part of the generated sql looks like:

AND CASE
WHEN (“Leavedate”.“SEASON” IS NULL)
OR (“Leavedate”.“SEASONYEAR” IS NULL)
THEN NULL
ELSE
(“Leavedate”.“SEASON” || “Leavedate”.“SEASONYEAR”)
END = ‘Z10’

Why is the check if the column is null.
I found a setting that Cognos uses DECODE instead of CASE, but I’m looking for a way to skip the check on IS NULL
Like this:

AND “Leavedate”.“SEASON” || “Leavedate”.“SEASONYEAR” = ‘Z10’

Any help would be appreciated.

Regards,

Emile

2

This is a known issue and it has not been fixed as of FixPack 3. Have not tried yet with FixPack4.
You can use concat function instead:
concat(SEASON,SEASONYEAR) = ‘Z10’

Not an ideal solution, but produces much cleaner code.

3

Hi Alex,

Thanks for your reply.
I haven’t tested with Fixpack 4, currently we only have Fixpack 2.

I did some tests with concat and it works.
The generated sql looks compacter and is less heavy on the database server.
The database server uses less CPU, the IO time is the same.

Regards,

Emile