ORACLE CASE WHEN Functionality#

The Oracle CASE..WHEN functionality may be invoked to provide very comprehensive decision making capability in derivation expressions, or other select statements.

Functionality:#

The CASE..WHEN logic may be invoked to return various values depending upon conditional logic built in to the expression.

Syntax:!

  CASE WHEN (expression 1) THEN value 1
       WHEN (expression 2) THEN value 2
        … etc …
       ELSE value n
  END

Operations: #

You would typically provide two options here, one or more WHEN / THEN pairs and optionally an ELSE condition. Note that the expressions do not need to reference the same variable set.

The parentheses around each expression must be included, and the END keyword as well.

The returned values in all cases should be of the same type.

Example: #

Using the UEEF function, where AS OF date is field number [350005] we can ascertain if a passed date field occurs within the month specified by the As Of date, by using this case..when construct:

CASE WHEN (to_date(~,'DD-Mon-YYYY') between 
           trunc(to_date(~[350005],'DD-Mon-YYYY'),'MM') and  
               last_day(to_date(~[350005],'DD-Mon-YYYY')))
     THEN 'C'
     ELSE 'P'
END

Breakdown of example:#

trunc(to_date([350005],'DD-Mon-YYYY'),'MM')

(A) This will take the passed As Of date and determine the first day of the month

last_day(to_date([350005],'DD-Mon-YYYY'))

(B) This will take the passed As Of date and determine the last day of the month

CASE WHEN (to_date(~,'DD-Mon-YYYY') between (result A) and (result B)

(C) will evaluate the variable date used if it is between the first and last day of the month and then return ‘C’, otherwise ‘P’

---

Notes #

Click to create a new notes page