Embedded SQL in PSQuery

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.

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

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License