Case When Exists SQL
The Case-When-Exists expression in Oracle is really handy. Here's an example of how to use it in a sub-select to return a status. This SQL checks for a match between the
PSOPRDEFN records to determine the person status. The idea is that if the operator is not in
PS_PERSON then they are not a true person in PeopleSoft.
Please be aware that this SQL will only work if:
- You are using PeopleSoft HRMS/CS 8.9 or above
- You are using an Oracle database
select O.OPRID, O.EMPLID, case when exists ( select 1 from PS_PERSON P where P.EMPLID = O.EMPLID ) then 'Person' else 'Not a Person' end as PERSON_STATUS from PSOPRDEFN O;