Is there a way to use the WITH Clause in Peoplesoft Query Manager?

Question

I use the WITH clause in Oracle SQL lots of times and knowing that more than 90% of the time, it performs faster, but since I am working on the Peoplesoft application, so I just wonder is there a way to use the WITH Clause in Peoplesoft Query Manager, too?

Answer 1

Put your SQL inside a Record View. Grant Query Tree security to the record view. SELECT * from your view via PS Query.

From memory, I seem to have also had some success wrapping the query as an inline view inside the Record View.

e.g. Record View SQL:

SELECT * FROM 
( 
<INSERT Common Table Expression here>
)

Using example Common Table Expression

with MYCTE AS (SELECT 1 as fake FROM DUAL) SELECT fake FROM MYCTE WHERE fake = 1

That would then become

SELECT * FROM 
( 
with MYCTE AS (SELECT 1 as fake FROM DUAL) SELECT fake FROM MYCTE WHERE fake = 1
)

Naturally, PeopleTools Application Designer will reformat the SQL as it sees fit when you save the definition.