2 Replies Latest reply: Mar 26, 2013 7:46 AM by AquaNX4 RSS

    Help - Nested Case Statement

    AquaNX4
      Hello everyone.  I am having one hell of a time trying to figure out my nested case statement.  It's pretty straightforward, but I always seem to get this error:

      ORA-00937: not a single-group group function
      *00937. 00000 - "not a single-group group function"*
      Cause:
      Action:

      However, when I try to enter a "group by" I get this error:

      ORA-00979: not a GROUP BY expression
      *00979. 00000 - "not a GROUP BY expression"*
      Cause:
      Action:

      I'm not sure if there is something wrong with the syntax of my nested case statement, or I am missing something more fundamental. This is my code:
      SELECT AE.EMP_ID,
        CASE
          WHEN EP.PHYSICAL_DATE IS NULL
          THEN
            CASE
              WHEN EC.ORIGINAL_CONTRACT_START < ((SYSDATE) - 365)
              THEN 'NEEDS PHYSICAL'
              WHEN EC.ORIGINAL_CONTRACT_START < ((SYSDATE) - 330)
              THEN 'COMING UP'
              ELSE 'No'
            END
          WHEN MAX(EP.PHYSICAL_DATE) KEEP (DENSE_RANK LAST
          ORDER BY EP.PHYSICAL_DATE) < ((SYSDATE) - 365)
          THEN 'NEEDS PHYSICAL'
          WHEN MAX(EP.PHYSICAL_DATE) KEEP (DENSE_RANK LAST
          ORDER BY EP.PHYSICAL_DATE) < ((SYSDATE) - 330)
          THEN 'COMING UP'
          ELSE 'No'
        END "Needs Physical?"
      FROM AP AE
      LEFT JOIN EMP_PHYSICAL EP
      ON AE.EMP_ID = EP.EMP_ID
      LEFT JOIN POSITION_OFFERED PO
      ON AE.EMP_ID = PO.EMP_ID
      LEFT JOIN EMP_CONTRACT EC
      ON AE.EMP_ID         = EC.EMP_ID
      WHERE PO.ACTUAL_END IS NULL
      AND (EP.PHYSICAL     = 1
      OR EP.PHYSICAL      IS NULL)
      AND :P71_EMP_ID = AE.EMP_ID;
      I appreciate any and all help. Have a good day.

      Aqua
        • 1. Re: Help - Nested Case Statement
          Chanchal Wankhade
          SELECT AE.EMP_ID,
          CASE
          WHEN EP.PHYSICAL_DATE IS NULL
          THEN
          CASE
          WHEN EC.ORIGINAL_CONTRACT_START < ((SYSDATE) - 365)
          THEN 'NEEDS PHYSICAL'
          WHEN EC.ORIGINAL_CONTRACT_START < ((SYSDATE) - 330)
          THEN 'COMING UP'
          ELSE 'No'
          END
          WHEN MAX(EP.PHYSICAL_DATE) KEEP (DENSE_RANK LAST
          ORDER BY EP.PHYSICAL_DATE) < ((SYSDATE) - 365)
          THEN 'NEEDS PHYSICAL'
          WHEN MAX(EP.PHYSICAL_DATE) KEEP (DENSE_RANK LAST
          ORDER BY EP.PHYSICAL_DATE) < ((SYSDATE) - 330)
          THEN 'COMING UP'
          ELSE 'No'
          END "Needs Physical?"
          FROM AP AE
          LEFT JOIN EMP_PHYSICAL EP
          ON AE.EMP_ID = EP.EMP_ID
          LEFT JOIN POSITION_OFFERED PO
          ON AE.EMP_ID = PO.EMP_ID
          LEFT JOIN EMP_CONTRACT EC
          ON AE.EMP_ID = EC.EMP_ID
          WHERE PO.ACTUAL_END IS NULL
          AND (EP.PHYSICAL = 1
          OR EP.PHYSICAL IS NULL)
          AND :P71_EMP_ID = AE.EMP_ID;
          GROUP BY AE.EMP_ID,EP.PHYSICAL_DATE,EC.ORIGINAL_CONTRACT_START;
          Hi,

          You need to add Group by clause at the end of the query i have added above.
          • 2. Re: Help - Nested Case Statement
            AquaNX4
            Incredible. I didn't realize you have to place the columns within the case statement in the "group by" clause. Incredible! Thank you so much. It works fine.

            Aqua