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

转载于:https://www.cnblogs.com/bryanchen/p/3385934.html