CASE Statements in PL/SQL examples

by Andy
Posted on February 4, 2018November 11, 2020

CASE Statements can be tricky in most languages. They can be even more difficult to get right in SQL. Here are some examples of CASE statements in PL/SQL to get you on the right path. Each example of a CASE statement in PL/SQL below has it’s own interesting syntax and uses. Using a CASE statement in PL/SQL is a good practice and makes code easier to read, modify and understand.

Standard CASE Statement Example

SELECT BUSINESS_UNIT, VOUCHER_ID,
/* Test the value after WHEN clause since using operators */
CASE WHEN MONTHS_BETWEEN(ENTERED_DT, NVL(RECEIPT_DT, TRUNC(SYSDATE))) <= 0
      THEN 'PO NOT RECD PRIOR TO INV'
WHEN MONTHS_BETWEEN(ENTERED_DT, NVL(RECEIPT_DT, TRUNC(SYSDATE))) <=.033
      THEN 'PO RECD DAY BEFORE INV'
      ELSE 'PO RECD PRIOR TO PO' END AS RECVD,
/* Test the value only once since not using operators – single value to compare */
CASE ORIGIN WHEN 'ONL' THEN 'ONLINE'
            WHEN 'BAT' THEN 'BATCH PROCESS'
            ELSE 'SOMEONE SCREWED UP MY SELECT STATEMENT' END AS ORIGIN
FROM PS_VOUCHER
WHERE ORIGIN IN('ONL', 'BAT') AND ENTERED_DT > SYSDATE - 30

SELECT BUSINESS_UNIT, VOUCHER_ID,

/* Test the value after WHEN clause since using operators */

CASE WHEN MONTHS_BETWEEN(ENTERED_DT, NVL(RECEIPT_DT, TRUNC(SYSDATE))) <= 0

THEN 'PO NOT RECD PRIOR TO INV'

WHEN MONTHS_BETWEEN(ENTERED_DT, NVL(RECEIPT_DT, TRUNC(SYSDATE))) <=.033

THEN 'PO RECD DAY BEFORE INV'

ELSE 'PO RECD PRIOR TO PO' END AS RECVD,

/* Test the value only once since not using operators – single value to compare */

CASE ORIGIN WHEN 'ONL' THEN 'ONLINE'

WHEN 'BAT' THEN 'BATCH PROCESS'

ELSE 'SOMEONE SCREWED UP MY SELECT STATEMENT' END AS ORIGIN

FROM PS_VOUCHER

WHERE ORIGIN IN('ONL', 'BAT') AND ENTERED_DT > SYSDATE - 30

CASE Statement in GROUP BY Example

SELECT COUNT(*),
CASE WHEN MONTHS_BETWEEN(ENTERED_DT, NVL(RECEIPT_DT, TRUNC(SYSDATE))) <= 0
          THEN 'PO NOT RECD PRIOR TO INV'
      WHEN MONTHS_BETWEEN(ENTERED_DT, NVL(RECEIPT_DT, TRUNC(SYSDATE))) <=.033
          THEN 'PO RECD DAY BEFORE INV'
      ELSE 'PO RECD PRIOR TO PO' END AS RECVD
FROM PS_VOUCHER
WHERE ORIGIN IN('ONL', 'BAT') AND ENTERED_DT > SYSDATE - 30
GROUP BY
      CASE WHEN MONTHS_BETWEEN(ENTERED_DT, NVL(RECEIPT_DT, TRUNC(SYSDATE))) <= 0
                THEN 'PO NOT RECD PRIOR TO INV'
           WHEN MONTHS_BETWEEN(ENTERED_DT, NVL(RECEIPT_DT, TRUNC(SYSDATE))) <=.033
                THEN 'PO RECD DAY BEFORE INV'
           ELSE 'PO RECD PRIOR TO PO' END

SELECT COUNT(*),

CASE WHEN MONTHS_BETWEEN(ENTERED_DT, NVL(RECEIPT_DT, TRUNC(SYSDATE))) <= 0

THEN 'PO NOT RECD PRIOR TO INV'

WHEN MONTHS_BETWEEN(ENTERED_DT, NVL(RECEIPT_DT, TRUNC(SYSDATE))) <=.033

THEN 'PO RECD DAY BEFORE INV'

ELSE 'PO RECD PRIOR TO PO' END AS RECVD

FROM PS_VOUCHER

WHERE ORIGIN IN('ONL', 'BAT') AND ENTERED_DT > SYSDATE - 30

GROUP BY

CASE WHEN MONTHS_BETWEEN(ENTERED_DT, NVL(RECEIPT_DT, TRUNC(SYSDATE))) <= 0

THEN 'PO NOT RECD PRIOR TO INV'

WHEN MONTHS_BETWEEN(ENTERED_DT, NVL(RECEIPT_DT, TRUNC(SYSDATE))) <=.033

THEN 'PO RECD DAY BEFORE INV'

ELSE 'PO RECD PRIOR TO PO' END

Nested CASE Statement in GROUP BY Example

SELECT COUNT(*),
/* Not using operators, so can use case statement like this */
CASE FIELDVALUE WHEN '1' THEN 'Chunk 1'
                WHEN '2' THEN XLATLONGNAME
                WHEN NULL THEN 'Null'
                ELSE 'Not Telling you' END XlatValue,
/* Using operators, so need to test FIELDVALUE in each WHEN statement */
CASE WHEN FIELDVALUE IN('1', '2') THEN 'Chunk Value <3'
     WHEN FIELDVALUE IS NULL OR FIELDVALUE = '5' THEN 'Invalid Value'
     ELSE 'Clueless' END XlatValue2
FROM PSXLATITEM
WHERE FIELDNAME = 'AE_CHUNK_LOAD'
GROUP BY
CASE FIELDVALUE WHEN '1' THEN 'Chunk 1'
     WHEN '2' THEN XLATLONGNAME
     WHEN NULL THEN 'Null'
     ELSE 'Not Telling you' END,
CASE WHEN FIELDVALUE IN('1', '2') THEN 'Chunk Value <3'
     WHEN FIELDVALUE IS NULL OR FIELDVALUE = '5' THEN 'Invalid Value'
     ELSE 'Clueless' END
SELECT OBJECTTYPE, OBJECTVALUE1,
CASE OBJECTTYPE
     WHEN 0 THEN CASE NVL((SELECT RECTYPE FROM PSRECDEFN WHERE RECNAME = I.OBJECTVALUE1), 99)
                      WHEN 0 THEN 'SQL Table in DB'
                      WHEN 1 THEN 'SQL View in DB'
                      WHEN 2 THEN 'Work Record'
                      WHEN 3 THEN 'Sub Record'
                      WHEN 5 THEN 'Dynamic View'
                      WHEN 6 THEN 'Query View'
                      WHEN 7 THEN 'Temporary Table'
                      ELSE 'Other Record or Deleted' END
      WHEN 1 THEN 'Index'
      WHEN 2 THEN 'Field'
      ELSE 'Just an Illustration' END AS NESTED_CASE_DEMO
FROM PSPROJECTITEM I
WHERE OBJECTTYPE IN(0, 1) AND OBJECTVALUE1 LIKE 'PSO%'
ORDER BY OBJECTTYPE, 3, OBJECTVALUE1

SELECT COUNT(*),

/* Not using operators, so can use case statement like this */

CASE FIELDVALUE WHEN '1' THEN 'Chunk 1'

WHEN '2' THEN XLATLONGNAME

WHEN NULL THEN 'Null'

ELSE 'Not Telling you' END XlatValue,

/* Using operators, so need to test FIELDVALUE in each WHEN statement */

CASE WHEN FIELDVALUE IN('1', '2') THEN 'Chunk Value <3'

WHEN FIELDVALUE IS NULL OR FIELDVALUE = '5' THEN 'Invalid Value'

ELSE 'Clueless' END XlatValue2

FROM PSXLATITEM

WHERE FIELDNAME = 'AE_CHUNK_LOAD'

GROUP BY

CASE FIELDVALUE WHEN '1' THEN 'Chunk 1'

WHEN '2' THEN XLATLONGNAME

WHEN NULL THEN 'Null'

ELSE 'Not Telling you' END,

CASE WHEN FIELDVALUE IN('1', '2') THEN 'Chunk Value <3'

WHEN FIELDVALUE IS NULL OR FIELDVALUE = '5' THEN 'Invalid Value'

ELSE 'Clueless' END

SELECT OBJECTTYPE, OBJECTVALUE1,

CASE OBJECTTYPE

WHEN 0 THEN CASE NVL((SELECT RECTYPE FROM PSRECDEFN WHERE RECNAME = I.OBJECTVALUE1), 99)

WHEN 0 THEN 'SQL Table in DB'

WHEN 1 THEN 'SQL View in DB'

WHEN 2 THEN 'Work Record'

WHEN 3 THEN 'Sub Record'

WHEN 5 THEN 'Dynamic View'

WHEN 6 THEN 'Query View'

WHEN 7 THEN 'Temporary Table'

ELSE 'Other Record or Deleted' END

WHEN 1 THEN 'Index'

WHEN 2 THEN 'Field'

ELSE 'Just an Illustration' END AS NESTED_CASE_DEMO

FROM PSPROJECTITEM I

WHERE OBJECTTYPE IN(0, 1) AND OBJECTVALUE1 LIKE 'PSO%'

ORDER BY OBJECTTYPE, 3, OBJECTVALUE1