Expression Functions PAGE 2
Northwestern University HR Operations, Reporting, and Analytics
IF-THEN-ELSE FUNCTION
DECODE
DECODE can be used as a simple “If-Then-Else” statement to test one field against one or more values.
SYNTAX
DECODE( expression , searchfor , result [, searchfor , result]... [, default] )
PARAMETERS
expression - the expression or field that you are testing
searchfor – the value that is compared against the expression
result – the value that is returned/displayed if expression = searchfor
default – (optional) the value that is returned when expression does not match any searchfor
EXAMPLE
DECODE(A.POSN_TYPE, 'FAC', 'Faculty', 'STF', 'Staff', 'STU', 'Grad Student', 'Temp')
CASE
CASE may be used for a more intricate If-Then-Else test, where multiple expressions, fields, or combinations of fields need to be
tested at the same time – or when the fields need to be tested against other expressions or fields.
SYNTAX
CASE [expression]
WHEN condition THEN result
[WHEN condition THEN result]...
[ELSE default]
END
PARAMETERS
expression – (optional) the field or expression to test, only if all WHEN statements are testing the same expression. This is
similar to using the
DECODE function.
condition – the value or full expression/field to test. Each condition must be the same datatype. Conditions must be
listed in order of importance; once one
condition is satisfied, its result is returned and no other condition will be tested.
result – the value that is returned/displayed if condition is satisfied.
default – (optional) the value that is returned when none of the conditions is satisfied.
EXAMPLES
A.POSN_TYPE
WHEN 'FAC' THEN 'Faculty'
WHEN 'STF' THEN 'Staff'
WHEN 'STU' THEN 'Grad Student'
ELSE 'Temp'
WHEN A.POSN_CATEGORY = 'RES' THEN 'Research Faculty'
WHEN A.POSN_TYPE = 'FAC' THEN 'Non-Research Faculty'
WHEN A.POSN_TYPE = 'STF' THEN 'Staff'
WHEN A.PAYGROUP = 'MGW' OR A.JOBCODE = '100029' OR
A.JOBTITLE LIKE '%WS' THEN 'Student'
WHEN A.JOBTITLE LIKE '%(SP)' THEN 'Special Pay'
WHEN A.PAYGROUP = 'BIT' THEN 'Temp'
ELSE A.JOBTITLE