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 inPS_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
;