Embedded SQL in PeopleSoft Query
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:
elect 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
- Add the fields
This gives us everything except for the
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.
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
Run the query (you may exceed the results set) but you should see the Person Status field with the value of either
Not a Person depending on whether or not they have an ID.