PS Query & SQL

I think I have my answer based on a few old threads (they all say it can't be done), but wanted to confirm. Has anyone successfully used expressions to embed a subquery in a SQL FROM clause in PSQuery?

We are trying to problem-solve an issue with an inadvertent Cartesian join in a longstanding query. One solution our DBA's have presented is to basically embed the entire current query in a "wrapper" something like this:

SELECT * FROM ( 

[Existing query with some additional code to pull ROW_NUMBER() as rn]

 )

WHERE rn = 1;

This is easy to do in direct SQL, but to accomplish it in PSQuery you have to embed the current query as a subquery in the FROM clause. There is no obvious way to do that and (as I mentioned) a couple of sources have said it's not possible.

I'm not going to get into specifics of the Cartesian join right now as that's a whole other thing, and we are pursuing a solution from that side. We also found a workaround for the immediate issue. But this "wrapper" technique could help in other scenarios and we wanted to know if it's possible in PS Query.

------------------------------
Aaron Minnick
Functional Lead / Sr. Systems Analyst
Ohio State University-Main Campus
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

Good Morning Aaron - 

As your reading of past emails indicates, I don't think you can  embed a FROM clause into a query using an Expression.  I will not say that categorically, because there are a lot of really smart people on this list, and one of them might have figured out a way to do this. 

At any rate, your request seems like a good use-case for the Composite Query tool.  If you have not used it before, Composite Query is a tool that allows the user to create a set of queries in (regular) Query Manager that serve as data sources.  Composite Query then allows you to join these queries together, and manipulate the output by adjusting criteria at the Composite level.  It is a pretty powerful tool, and has been updated recently to make it even more user friendly. 

If you are interested in exploring this tool a little more, Dan Labrecque and I did a webinar on Composite Query at the end of July, 2025.  You can review the presentation via this link -

• https://www.heug.org/viewdocument/using-peoplesoft-composite-query?CommunityKey=48418c7a-a7cf-4186-9bbf-01946b6e7556&tab=librarydocuments 

Also, there has been a number of further discussions of CQ on this list, where other (very smart) people have added to the discussion.  

Good Luck! 

------------------------------
Tom Johnson
Sr Business Systems Analyst
Duke University
tom.johnson@duke.edu
"None of us is as smart as all of us"
------------------------------

Original Message:
Sent: 12/19/2025 6:37:00 AM
From: Tom Johnson
Subject: RE: Use subquery in FROM clause?

Good Morning Aaron - 

As your reading of past emails indicates, I don't think you can  embed a FROM clause into a query using an Expression.  I will not say that categorically, because there are a lot of really smart people on this list, and one of them might have figured out a way to do this. 

At any rate, your request seems like a good use-case for the Composite Query tool.  If you have not used it before, Composite Query is a tool that allows the user to create a set of queries in (regular) Query Manager that serve as data sources.  Composite Query then allows you to join these queries together, and manipulate the output by adjusting criteria at the Composite level.  It is a pretty powerful tool, and has been updated recently to make it even more user friendly. 

If you are interested in exploring this tool a little more, Dan Labrecque and I did a webinar on Composite Query at the end of July, 2025.  You can review the presentation via this link -

• https://www.heug.org/viewdocument/using-peoplesoft-composite-query?CommunityKey=48418c7a-a7cf-4186-9bbf-01946b6e7556&tab=librarydocuments 

Also, there has been a number of further discussions of CQ on this list, where other (very smart) people have added to the discussion.  

Good Luck! 

------------------------------
Tom Johnson
Sr Business Systems Analyst
Duke University
tom.johnson@duke.edu
"None of us is as smart as all of us"
------------------------------

Original Message:
Sent: 12/19/2025 6:37:00 AM
From: Tom Johnson
Subject: RE: Use subquery in FROM clause?

Good Morning Aaron - 

As your reading of past emails indicates, I don't think you can  embed a FROM clause into a query using an Expression.  I will not say that categorically, because there are a lot of really smart people on this list, and one of them might have figured out a way to do this. 

At any rate, your request seems like a good use-case for the Composite Query tool.  If you have not used it before, Composite Query is a tool that allows the user to create a set of queries in (regular) Query Manager that serve as data sources.  Composite Query then allows you to join these queries together, and manipulate the output by adjusting criteria at the Composite level.  It is a pretty powerful tool, and has been updated recently to make it even more user friendly. 

If you are interested in exploring this tool a little more, Dan Labrecque and I did a webinar on Composite Query at the end of July, 2025.  You can review the presentation via this link -

• https://www.heug.org/viewdocument/using-peoplesoft-composite-query?CommunityKey=48418c7a-a7cf-4186-9bbf-01946b6e7556&tab=librarydocuments 

Also, there has been a number of further discussions of CQ on this list, where other (very smart) people have added to the discussion.  

Good Luck! 

------------------------------
Tom Johnson
Sr Business Systems Analyst
Duke University
tom.johnson@duke.edu
"None of us is as smart as all of us"
------------------------------

An expression within PS Query will not allow a FROM clause.   While it would seem simple to just build the inline Query in an expression and list it as your first field, the PS Query tool itself is blocking the use of a FROM clause.  If you try creating an expression with a FROM clause in it, you will receive an error (50,536).

I have not had the opportunity to work with Composite Queries in my role, but I agree with Tom that you may be able to use that tool to accomplish it.  And, if you can't figure out a way to do it with that tool, you could always look at having your development team create you a bolt-on view that could be provided to use within PS Query.

------------------------------
Mark Miszewski
Senior PeopleSoft Developer
Cornell University
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

Original Message:
Sent: 12/19/2025 8:00:00 AM
From: Mark Miszewski
Subject: RE: Use subquery in FROM clause?

An expression within PS Query will not allow a FROM clause.   While it would seem simple to just build the inline Query in an expression and list it as your first field, the PS Query tool itself is blocking the use of a FROM clause.  If you try creating an expression with a FROM clause in it, you will receive an error (50,536).

I have not had the opportunity to work with Composite Queries in my role, but I agree with Tom that you may be able to use that tool to accomplish it.  And, if you can't figure out a way to do it with that tool, you could always look at having your development team create you a bolt-on view that could be provided to use within PS Query.

------------------------------
Mark Miszewski
Senior PeopleSoft Developer
Cornell University
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

I'll decline to post the actual query for a few reasons. Our DBA's already reviewed it, and their solution was to put the wrapper around it I described. It calls a custom view which actually is causing the Cartesian join and we worked around that specific issue by using a delivered table. We mostly wanted to use this as a springboard to future work, to determine if this type of wrapper is possible in similar scenarios. But thank you!

------------------------------
Aaron Minnick
Functional Lead / Sr. Systems Analyst
Ohio State University-Main Campus
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

Original Message:
Sent: 12-19-2025 08:09 AM
From: Dana Pawlowicz
Subject: Use subquery in FROM clause?

Would you mind sharing your query and letting us know what issues you are having with it?  What's your end goal. What are you trying to accomplish?

There might be another way.

 

We've had issues with queries that run great and then all of a sudden run bad.  I think the one that was the worst when from under a minute to 13 hours.  It was attached to a "bad plan."  Our DBA runs "query tuner" on it.    This is what she gave me once to share with others.  If your DBA hasn't tried it on your problem query, I'd request it.  Sometimes it works, sometimes it doesn't.  We've also had issues with some delivered indexes that we've deleted at our school.  They come back with PeopleTools updates so we have to watch out for them.  I know those are for ITEM_SF.

 

I've attached the slides that my DBA gave me for a presentation I did at Alliance last year.   

 

 

 

Original Message:
Sent: 12/19/2025 8:00:00 AM
From: Mark Miszewski
Subject: RE: Use subquery in FROM clause?

An expression within PS Query will not allow a FROM clause.   While it would seem simple to just build the inline Query in an expression and list it as your first field, the PS Query tool itself is blocking the use of a FROM clause.  If you try creating an expression with a FROM clause in it, you will receive an error (50,536).

I have not had the opportunity to work with Composite Queries in my role, but I agree with Tom that you may be able to use that tool to accomplish it.  And, if you can't figure out a way to do it with that tool, you could always look at having your development team create you a bolt-on view that could be provided to use within PS Query.

------------------------------
Mark Miszewski
Senior PeopleSoft Developer
Cornell University
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

Aaron,

In my experience, I've never been able to create a query in PSQuery without at least one record in the FROM statement. 

However, there may still be a solution to your issue. Here at CU, we have a record named PTIA_DUAL. It has a single field named PTIA_DUMMY which always returns a value of 'X'. If you also have it, you may be able to do something like:

SELECT * 

FROM A.PTIA_DUAL

WHERE A.PTIA_DUMMY = SUBQUERY (insert your subquery, which would return the value 'X' instead of m)

;

Hope this helps,

------------------------------
Scott Frey
Senior Data Analyst - Human Resource Information Systems
University of Colorado System
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------