0 Replies Latest reply: Mar 26, 2013 8:30 AM by AquaNX4 RSS

    HELP - Nested Case Question (Multiple Rows)

    AquaNX4
      OK, Now that the syntax has been corrected with the help of "Chanchal Wankhade" (Thank you very much!), I have an entirely new issue. I am sure this issue has to do with my case statement logic. I am getting multiple rows, when I am only looking for one. Here is my code:
      SELECT 
        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 EP.PHYSICAL_DATE IS NOT NULL
          THEN 
            CASE 
              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
        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 EP.PHYSICAL_DATE, EC.ORIGINAL_CONTRACT_START;
      The OUTPUT is:
      Needs Physical?
      Row 1 NEEDS PHYSICAL
      Row 2 No

      However, only one of these rows should be the output, which is "No". How do you get a nested case statement to evaluate to one result, instead of multiple? I'm quite sure it is in the logic. To spell it out, this is what I am trying to accomplish with the above code:
      If the "EP.PHYSICAL_DATE" is null, then use these sets of formula's to evalute the output, BUT if the "EP.PHYSICAL_DATE" is not null, then use these set's of formula's to evaluate the output.

      As it stands now, it appears as if my nested case statement is doing exactly what I told it to do, which is to evaluate both conditions, and output both.

      Any help would be appreciated. Thanks.

      Aqua

      Edited by: AquaNX4 on Mar 26, 2013 6:30 AM