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.