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

 NOTE: you need to explicitly code in the join in the DETERMINE_PERSON_STATUS SQL - the line where P.EMPLID = A.EMPLID. The alias, A may be different depending on the table you are joining too.