CASE statements not working at all

[halkosj]

We've been having all kinds of problems with CASE statements:


In a query, we insert a DataItem and define it with this expression:

CASE ?prompt_response?
WHEN 'A' THEN Column_A
WHEN 'B' THEN Column_B
ELSE Column_C
END

When we run the report, the prompt page comes as usual. We select the prompt value and click "Finish".  The animated icon comes up and tells us the report is running.  It stays that way forever.  No results are ever returned.

When we "Show Generated SQL/MDX", again it hangs and never gives any SQL/MDX.

We found out that if we convert the CASE to IF..THEN ..ELSE, it works fine and the report is produced in less than a second.

There are several other scenarios we've encountered, all using CASE statements, which fail to work properly until we convert to IF ..THEN..ELSE

Is this a known bug in 8.3?

[blom0344]

Did you try reworking the simple case into a search case:

CASE WHEN ?prompt_response? = 'A'
THEN Column_A
WHEN ?prompt_response? = 'B'
THEN Column_B
ELSE Column_C
END

this syntax matches the syntax used for every RDBMS (well, you never know)

[Skykit]

Probably wont help but during a sorting of a chart, I used
Case [column_name]
When 'Jul' then 'A'
When 'Aug' then 'B'
When 'Sep' then 'C'
When 'Oct' then 'D'
When 'Nov' then 'E'
When 'Dec' then 'F'
When 'Jan' then 'G'
When 'Feb' then 'H'
When 'Mar' then 'I'
When 'Apr' then 'J'
When 'May' then 'K'
When 'Jun' then 'L'
End

this worked fine for me

[cognosjon]

I've not had this issue for some time now, but I have also experienced the CASE statement ceasing to function issue. If i recall at the time I was using 8.1 and 8.2. I also remember speaking to Cognos support who advised me to use an IF statement instead.

Every time I now write a report and start to use the Case statement I have this horrible feeling of impending doom when i attempt to run the report!! :o

[halkosj]

I found an IBM Technote on this that explains it very simply:

"MDX does not support the CASE statement."

They should really put this stuff in the User's Guide.

Here's the reference:

http://www-01.ibm.com/support/docview.wss?rs=3442&context=SS9RTN&context=SSWGNW&q1=case&uid=swg21342128&loc=en_US&cs=utf-8&lang=en