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 PS_PERSON and 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;
sql
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License