SQL | Using the ‘Decode’ and ‘Case’ statements in queries.
A. Why is SQL Important?
Although Barefoot PeopleSoft is mainly concerned about PeopleSoft (and the PeopleTools environment for development), in some cases, having a better understanding of SQL helps to make life easier. You might want to write some SQL to troubleshoot a problem, or you might be making a one-off data fix or creating a data conversion routine. Furthermore, code written in SQL is invariably faster than the equivalent code written in PeopleCode. So if performance is a key requirement (and future maintenance less of a requirement), writing code in SQL becomes an attractive option. Therefore, we will occasionally feature some native SQL functionality. And by ‘native’, I am referring to the Oracle ‘flavour’ of SQL. All these SQL examples assume you are working on an Oracle database.
We start with two functions that perform a similar task: setting the value of a field based on a set of pre-defined conditions.
B. Decode statement
The ‘decode’ statement works as a kind of an extended If-else statement. It tests for a variety of conditions, setting the field depending on which condition has been met.
The decode statement works in three parts:
decode(field_name, value1, result1, value2, result2,
value3, result3 ... result-default)
- field_name: this specifies the field you want to use as the basis for making the checks.
- value / result pairings: a list of one or more pairings which specify the value to check, along with the result to use for that value. Note that the value must be a single, defined constant such as ‘Y’ or ‘100’. The ‘decode’ statement does not handle more advanced logical constructs, such as ‘less than 10’, ‘not equal to X’, ‘exists in table X’.
- result-default: an optional field that specifies the value to use if none of the previous combinations were matched. If this field is left out of the statement, and no match is found for any of the pairings, then a null value will be returned by the statement.
Here’s a simple version of the ‘decode’ statement, checking the value of a flag field. If yes, some additional text is displayed in the third column. If the flag is ‘N’, no text is displayed.
select country, descr, decode(eu_member_state, 'Y', 'Part of EU', ' ') as EU_text from ps_country_tbl order by descr;
And here’s a slightly longer example, using a range of values from ‘1’ to ‘5’:
select student_id,
grade,
decode(grade, '5', 'Distinction', '4', 'Credit', '3', 'Pass', '2',
'Fail', '1', 'Low Fail', 'Other') as grade_type
from results;
C. Case statement
The ‘case’ statement is similar to ‘decode’, except that it gives you the ability to enter logical conditions as part of each ‘when’ clause:
select custid,
case when account_balance < 0 then 'WE OWE THEM'
when account_balance > 0 then 'THEY OWE US'
else 'EVERYONE IS HAPPY'
end as current_status
from account;
Each ‘when’ clause must be capable of having a True/False result, so this gives you a lot of power in coding more complex conditions:
select custid,
case when (country in ('USA', 'CAN', 'MEX')) then 'North American'
when (exists (select 'x' from ps_country_tbl c
where c.country = a.country
and c.eu_member_state = 'Y')) then 'EU Member'
when (country = 'AUS' or country = 'NZL') then 'Aust/NZ'
else 'Rest of the World'
end as descr
from addresses a;
Similar to the decode, we can specify a default condition to use via the use of the ‘else’ statement. And similar to the ‘decode’, if no ‘else’ clause is specified and no match found in the preceding ‘when’ clauses, the end result will be a null value.