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.

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! 

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.

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! 

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.

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.

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.

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.

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.

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.

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.