3 Replies Latest reply: Aug 2, 2012 3:49 AM by user8655468

Problem with CASE statements

Hi All

I have a calulation in a Discoverer Desktop report with 29 CASE statements and still have 2 more case statements to add. The problem is that my report freezes and fails to run because of too many case statements. I need to put 31 case statemnts in the report, how can I achieve this? Is there another way of doing it without causing the report to freeze?

Below is part of my case statements just to show you what I am trying to do:

CASE WHEN Ex = 'MM' AND Result IN ('D','M') THEN NULL ELSE
CASE WHEN Ex = 'PP' AND Result IN (40,35,30,25) THEN NULL ELSE
CASE WHEN Ex = 'LL' AND Result IN ('A','B','C','D') THEN NULL ELSE
CASE WHEN Ex = 'IO' AND Result IN ('Distinction','Merit') THEN NULL ELSE
CASE WHEN Ex IN ('ZZ','WW','SS','KK') AND Result IN ('PASS','P') THEN NULL ELSE .......................

The last statement should be CASE WHEN.... THEN NULL ELSE 1 END END END END ...........

• 1. Re: Problem with CASE statements
Hello
You may have hit the limit of characters in a calculation.

Try changing it into a single statement like this:

CASE WHEN Ex = 'MM' AND Result IN ('D','M') THEN NULL ELSE
WHEN Ex = 'PP' AND Result IN (40,35,30,25) THEN NULL ELSE
WHEN Ex = 'LL' AND Result IN ('A','B','C','D') THEN NULL ELSE
WHEN Ex = 'IO' AND Result IN ('Distinction','Merit') THEN NULL ELSE
WHEN Ex IN ('ZZ','WW','SS','KK') AND Result IN ('PASS','P') THEN NULL ELSE .......................

The last statement should be CASE WHEN.... THEN NULL ELSE 1 END

Another option is to combine all of the NULL statements into a series of OR options like this:

CASE WHEN
(Ex = 'MM' AND Result IN ('D','M')) OR
(Ex = 'PP' AND Result IN (40,35,30,25)) OR
(Ex = 'LL' AND Result IN ('A','B','C','D')) OR
(Ex = 'IO' AND Result IN ('Distinction','Merit')) OR
(Ex IN ('ZZ','WW','SS','KK') AND Result IN ('PASS','P')) THEN NULL ELSE .......................

Best wishes
Michael
• 2. Re: Problem with CASE statements
Thanks for the solution Michael. I used the first option and it worked well.
• 3. Re: Problem with CASE statements
Sorry I had forgotten to say when I used the 1st option of the solutions that you suggested I removed the ELSE part of it as it was giving error with the ELSE, so instead of

CASE WHEN Ex = 'MM' AND Result IN ('D','M') THEN NULL ELSE
WHEN Ex = 'PP' AND Result IN (40,35,30,25) THEN NULL ELSE
WHEN Ex = 'LL' AND Result IN ('A','B','C','D') THEN NULL ELSE
WHEN Ex = 'IO' AND Result IN ('Distinction','Merit') THEN NULL ELSE
WHEN Ex IN ('ZZ','WW','SS','KK') AND Result IN ('PASS','P') THEN NULL ELSE

I used

CASE WHEN Ex = 'MM' AND Result IN ('D','M') THEN NULL
WHEN Ex = 'PP' AND Result IN (40,35,30,25) THEN NULL
WHEN Ex = 'LL' AND Result IN ('A','B','C','D') THEN NULL
WHEN Ex = 'IO' AND Result IN ('Distinction','Merit') THEN NULL
WHEN Ex IN ('ZZ','WW','SS','KK') AND Result IN ('PASS','P') THEN NULL ELSE 1 END

Thank you so much Michael.