Turns out that you can embed SQL into your PeopleSoft Query using the Meta-SQL operator, %Sql. Here's a simple example using the information provided in the Case-When-Exists SQL article. Basically, we want to turn the following SQL into a PeopleSoft Query:
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;
The catch is that we need the case-when-exists block as a sub-select and you can't have a from clause in an expression. So here are the steps:
- Create a new query
- Add the record PSOPRDEFN
- Add the fields OPRID and EMPLID
This gives us everything except for the case-when-exists block.
Open application designer, and create a new SQL definition. Add the following SQL to the definition:
case when exists ( select 1 from PS_PERSON P where P.EMPLID = A.EMPLID ) then 'Person' else 'Not a Person' end as PERSON_STATUS
Save the definition with an appropriate name (e.g. DETERMINE_PERSON_STATUS).
Go back to your query:
- Add an expression, make the length 12 characters (length of the words Not a Person)
- In the expression text, type %SQL(DETERMINE_PERSON_STATUS) (the meta-sql to expand the SQL you created)
- Click the Use as a Field hyperlink to add the expression as a field
- In the Field tab, click on the expression and change the heading text to something more friendly, e.g. Person Status.
View your SQL to confirm it shows up with the case-when-exists block.
Run the query (you may exceed the results set) but you should see the Person Status field with the value of either Person or Not a Person depending on whether or not they have an ID.