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

    Problem with CASE statements

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

      Please help. Thanks.
        • 1. Re: Problem with CASE statements
          Michael Armstrong-Smith
          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
            user8655468
            Thanks for the solution Michael. I used the first option and it worked well.
            • 3. Re: Problem with CASE statements
              user8655468
              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.