This discussion is archived
2 Replies Latest reply: Mar 26, 2013 5:46 AM by 972703 RSS

Help - Nested Case Statement

972703 Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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
    972703 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points