PS Query & SQL

I would probably create a new view within Application designer that does all the joins/ unions, then add that view to the Query Tree security, then write the PSQuery using the new view.

Hey Audrey - As an alternative to using a Union, or creating a new view, you could also use the Composite Query tool.  This tool allows you to create a single data source, pulled from other 'base' queries built in Query Manager.  It accomplishes many of the things you need to do with a Union, and (in my experience) it runs pretty quickly.

Here are a couple of notes on Composite Query.

1. Navigation = Main Menu> Reporting Tools> Composite Query> Composite Query Manager.
2. You will need to have security for this functionality - we  used the delivered Roles Composite Query Developer or Composite Query User. 
3. The learning curve for Composite query is not too steep.  There are a set of little things to know, but those are documented in a variety of presentations and PeopleBooks.
   1. The biggest difference for me is that the regular, 'base' queries cannot have prompts.  You add the prompts at the Composite Query level.  
4. I like to think of Composite Queries as data sources, similar to what we build with BI Publisher. 

Good Luck, and I am happy to answer more questions... 

I would probably create a new view within Application designer that does all the joins/ unions, then add that view to the Query Tree security, then write the PSQuery using the new view.

I use Composite Query and Tom is absolutely correct.  I created a very large one that would always time out on me.  However, as of PeopleTools 8.60, you can now schedule Composite Query:

In PeopleTools 8.60, PeopleSoft Query introduces the Composite Query Scheduler, which is used to schedule a composite query to run in the PeopleSoft Process Scheduler. 

You can search for an existing run control ID or create a new run control ID and schedule a composite query using the Schedule Composite Query page, check the status of your request using the Process Monitor link, and view your output using the Report Manager link.

I can now schedule my Composite Query and not have to worry about it timing out.  So if you run into that issue, you have this option available.

Hey Audrey - As an alternative to using a Union, or creating a new view, you could also use the Composite Query tool.  This tool allows you to create a single data source, pulled from other 'base' queries built in Query Manager.  It accomplishes many of the things you need to do with a Union, and (in my experience) it runs pretty quickly.

Here are a couple of notes on Composite Query.

1. Navigation = Main Menu> Reporting Tools> Composite Query> Composite Query Manager.
2. You will need to have security for this functionality - we  used the delivered Roles Composite Query Developer or Composite Query User. 
3. The learning curve for Composite query is not too steep.  There are a set of little things to know, but those are documented in a variety of presentations and PeopleBooks.
   1. The biggest difference for me is that the regular, 'base' queries cannot have prompts.  You add the prompts at the Composite Query level.  
4. I like to think of Composite Queries as data sources, similar to what we build with BI Publisher. 

Good Luck, and I am happy to answer more questions... 

I use Composite Query and Tom is absolutely correct.  I created a very large one that would always time out on me.  However, as of PeopleTools 8.60, you can now schedule Composite Query:

In PeopleTools 8.60, PeopleSoft Query introduces the Composite Query Scheduler, which is used to schedule a composite query to run in the PeopleSoft Process Scheduler. 

You can search for an existing run control ID or create a new run control ID and schedule a composite query using the Schedule Composite Query page, check the status of your request using the Process Monitor link, and view your output using the Report Manager link.

I can now schedule my Composite Query and not have to worry about it timing out.  So if you run into that issue, you have this option available.

I use Composite Query and Tom is absolutely correct.  I created a very large one that would always time out on me.  However, as of PeopleTools 8.60, you can now schedule Composite Query:

In PeopleTools 8.60, PeopleSoft Query introduces the Composite Query Scheduler, which is used to schedule a composite query to run in the PeopleSoft Process Scheduler. 

You can search for an existing run control ID or create a new run control ID and schedule a composite query using the Schedule Composite Query page, check the status of your request using the Process Monitor link, and view your output using the Report Manager link.

I can now schedule my Composite Query and not have to worry about it timing out.  So if you run into that issue, you have this option available.

I use Composite Query and Tom is absolutely correct.  I created a very large one that would always time out on me.  However, as of PeopleTools 8.60, you can now schedule Composite Query:

In PeopleTools 8.60, PeopleSoft Query introduces the Composite Query Scheduler, which is used to schedule a composite query to run in the PeopleSoft Process Scheduler. 

You can search for an existing run control ID or create a new run control ID and schedule a composite query using the Schedule Composite Query page, check the status of your request using the Process Monitor link, and view your output using the Report Manager link.

I can now schedule my Composite Query and not have to worry about it timing out.  So if you run into that issue, you have this option available.

Hey Audrey - As an alternative to using a Union, or creating a new view, you could also use the Composite Query tool.  This tool allows you to create a single data source, pulled from other 'base' queries built in Query Manager.  It accomplishes many of the things you need to do with a Union, and (in my experience) it runs pretty quickly.

Here are a couple of notes on Composite Query.

1. Navigation = Main Menu> Reporting Tools> Composite Query> Composite Query Manager.
2. You will need to have security for this functionality - we  used the delivered Roles Composite Query Developer or Composite Query User. 
3. The learning curve for Composite query is not too steep.  There are a set of little things to know, but those are documented in a variety of presentations and PeopleBooks.
   1. The biggest difference for me is that the regular, 'base' queries cannot have prompts.  You add the prompts at the Composite Query level.  
4. I like to think of Composite Queries as data sources, similar to what we build with BI Publisher. 

Good Luck, and I am happy to answer more questions... 

I would probably create a new view within Application designer that does all the joins/ unions, then add that view to the Query Tree security, then write the PSQuery using the new view.

I use Composite Query and Tom is absolutely correct.  I created a very large one that would always time out on me.  However, as of PeopleTools 8.60, you can now schedule Composite Query:

In PeopleTools 8.60, PeopleSoft Query introduces the Composite Query Scheduler, which is used to schedule a composite query to run in the PeopleSoft Process Scheduler. 

You can search for an existing run control ID or create a new run control ID and schedule a composite query using the Schedule Composite Query page, check the status of your request using the Process Monitor link, and view your output using the Report Manager link.

I can now schedule my Composite Query and not have to worry about it timing out.  So if you run into that issue, you have this option available.

Hey Audrey - As an alternative to using a Union, or creating a new view, you could also use the Composite Query tool.  This tool allows you to create a single data source, pulled from other 'base' queries built in Query Manager.  It accomplishes many of the things you need to do with a Union, and (in my experience) it runs pretty quickly.

Here are a couple of notes on Composite Query.

1. Navigation = Main Menu> Reporting Tools> Composite Query> Composite Query Manager.
2. You will need to have security for this functionality - we  used the delivered Roles Composite Query Developer or Composite Query User. 
3. The learning curve for Composite query is not too steep.  There are a set of little things to know, but those are documented in a variety of presentations and PeopleBooks.
   1. The biggest difference for me is that the regular, 'base' queries cannot have prompts.  You add the prompts at the Composite Query level.  
4. I like to think of Composite Queries as data sources, similar to what we build with BI Publisher. 

Good Luck, and I am happy to answer more questions... 

I would probably create a new view within Application designer that does all the joins/ unions, then add that view to the Query Tree security, then write the PSQuery using the new view.

I use Composite Query and Tom is absolutely correct.  I created a very large one that would always time out on me.  However, as of PeopleTools 8.60, you can now schedule Composite Query:

In PeopleTools 8.60, PeopleSoft Query introduces the Composite Query Scheduler, which is used to schedule a composite query to run in the PeopleSoft Process Scheduler. 

You can search for an existing run control ID or create a new run control ID and schedule a composite query using the Schedule Composite Query page, check the status of your request using the Process Monitor link, and view your output using the Report Manager link.

I can now schedule my Composite Query and not have to worry about it timing out.  So if you run into that issue, you have this option available.

Hey Audrey - As an alternative to using a Union, or creating a new view, you could also use the Composite Query tool.  This tool allows you to create a single data source, pulled from other 'base' queries built in Query Manager.  It accomplishes many of the things you need to do with a Union, and (in my experience) it runs pretty quickly.

Here are a couple of notes on Composite Query.

1. Navigation = Main Menu> Reporting Tools> Composite Query> Composite Query Manager.
2. You will need to have security for this functionality - we  used the delivered Roles Composite Query Developer or Composite Query User. 
3. The learning curve for Composite query is not too steep.  There are a set of little things to know, but those are documented in a variety of presentations and PeopleBooks.
   1. The biggest difference for me is that the regular, 'base' queries cannot have prompts.  You add the prompts at the Composite Query level.  
4. I like to think of Composite Queries as data sources, similar to what we build with BI Publisher. 

Good Luck, and I am happy to answer more questions... 

I would probably create a new view within Application designer that does all the joins/ unions, then add that view to the Query Tree security, then write the PSQuery using the new view.

I use Composite Query and Tom is absolutely correct.  I created a very large one that would always time out on me.  However, as of PeopleTools 8.60, you can now schedule Composite Query:

In PeopleTools 8.60, PeopleSoft Query introduces the Composite Query Scheduler, which is used to schedule a composite query to run in the PeopleSoft Process Scheduler. 

You can search for an existing run control ID or create a new run control ID and schedule a composite query using the Schedule Composite Query page, check the status of your request using the Process Monitor link, and view your output using the Report Manager link.

I can now schedule my Composite Query and not have to worry about it timing out.  So if you run into that issue, you have this option available.

Hey Audrey - As an alternative to using a Union, or creating a new view, you could also use the Composite Query tool.  This tool allows you to create a single data source, pulled from other 'base' queries built in Query Manager.  It accomplishes many of the things you need to do with a Union, and (in my experience) it runs pretty quickly.

Here are a couple of notes on Composite Query.

1. Navigation = Main Menu> Reporting Tools> Composite Query> Composite Query Manager.
2. You will need to have security for this functionality - we  used the delivered Roles Composite Query Developer or Composite Query User. 
3. The learning curve for Composite query is not too steep.  There are a set of little things to know, but those are documented in a variety of presentations and PeopleBooks.
   1. The biggest difference for me is that the regular, 'base' queries cannot have prompts.  You add the prompts at the Composite Query level.  
4. I like to think of Composite Queries as data sources, similar to what we build with BI Publisher. 

Good Luck, and I am happy to answer more questions... 

I would probably create a new view within Application designer that does all the joins/ unions, then add that view to the Query Tree security, then write the PSQuery using the new view.

I use Composite Query and Tom is absolutely correct.  I created a very large one that would always time out on me.  However, as of PeopleTools 8.60, you can now schedule Composite Query:

In PeopleTools 8.60, PeopleSoft Query introduces the Composite Query Scheduler, which is used to schedule a composite query to run in the PeopleSoft Process Scheduler. 

You can search for an existing run control ID or create a new run control ID and schedule a composite query using the Schedule Composite Query page, check the status of your request using the Process Monitor link, and view your output using the Report Manager link.

I can now schedule my Composite Query and not have to worry about it timing out.  So if you run into that issue, you have this option available.

Hey Audrey - As an alternative to using a Union, or creating a new view, you could also use the Composite Query tool.  This tool allows you to create a single data source, pulled from other 'base' queries built in Query Manager.  It accomplishes many of the things you need to do with a Union, and (in my experience) it runs pretty quickly.

Here are a couple of notes on Composite Query.

1. Navigation = Main Menu> Reporting Tools> Composite Query> Composite Query Manager.
2. You will need to have security for this functionality - we  used the delivered Roles Composite Query Developer or Composite Query User. 
3. The learning curve for Composite query is not too steep.  There are a set of little things to know, but those are documented in a variety of presentations and PeopleBooks.
   1. The biggest difference for me is that the regular, 'base' queries cannot have prompts.  You add the prompts at the Composite Query level.  
4. I like to think of Composite Queries as data sources, similar to what we build with BI Publisher. 

Good Luck, and I am happy to answer more questions... 

I would probably create a new view within Application designer that does all the joins/ unions, then add that view to the Query Tree security, then write the PSQuery using the new view.

I would love to be part of the webinar as well!

I use Composite Query and Tom is absolutely correct.  I created a very large one that would always time out on me.  However, as of PeopleTools 8.60, you can now schedule Composite Query:

In PeopleTools 8.60, PeopleSoft Query introduces the Composite Query Scheduler, which is used to schedule a composite query to run in the PeopleSoft Process Scheduler. 

You can search for an existing run control ID or create a new run control ID and schedule a composite query using the Schedule Composite Query page, check the status of your request using the Process Monitor link, and view your output using the Report Manager link.

I can now schedule my Composite Query and not have to worry about it timing out.  So if you run into that issue, you have this option available.

Hey Audrey - As an alternative to using a Union, or creating a new view, you could also use the Composite Query tool.  This tool allows you to create a single data source, pulled from other 'base' queries built in Query Manager.  It accomplishes many of the things you need to do with a Union, and (in my experience) it runs pretty quickly.

Here are a couple of notes on Composite Query.

1. Navigation = Main Menu> Reporting Tools> Composite Query> Composite Query Manager.
2. You will need to have security for this functionality - we  used the delivered Roles Composite Query Developer or Composite Query User. 
3. The learning curve for Composite query is not too steep.  There are a set of little things to know, but those are documented in a variety of presentations and PeopleBooks.
   1. The biggest difference for me is that the regular, 'base' queries cannot have prompts.  You add the prompts at the Composite Query level.  
4. I like to think of Composite Queries as data sources, similar to what we build with BI Publisher. 

Good Luck, and I am happy to answer more questions... 

I would love to be part of the webinar as well!

I use Composite Query and Tom is absolutely correct.  I created a very large one that would always time out on me.  However, as of PeopleTools 8.60, you can now schedule Composite Query:

In PeopleTools 8.60, PeopleSoft Query introduces the Composite Query Scheduler, which is used to schedule a composite query to run in the PeopleSoft Process Scheduler. 

You can search for an existing run control ID or create a new run control ID and schedule a composite query using the Schedule Composite Query page, check the status of your request using the Process Monitor link, and view your output using the Report Manager link.

I can now schedule my Composite Query and not have to worry about it timing out.  So if you run into that issue, you have this option available.

Hey Audrey - As an alternative to using a Union, or creating a new view, you could also use the Composite Query tool.  This tool allows you to create a single data source, pulled from other 'base' queries built in Query Manager.  It accomplishes many of the things you need to do with a Union, and (in my experience) it runs pretty quickly.

Here are a couple of notes on Composite Query.

1. Navigation = Main Menu> Reporting Tools> Composite Query> Composite Query Manager.
2. You will need to have security for this functionality - we  used the delivered Roles Composite Query Developer or Composite Query User. 
3. The learning curve for Composite query is not too steep.  There are a set of little things to know, but those are documented in a variety of presentations and PeopleBooks.
   1. The biggest difference for me is that the regular, 'base' queries cannot have prompts.  You add the prompts at the Composite Query level.  
4. I like to think of Composite Queries as data sources, similar to what we build with BI Publisher. 

Good Luck, and I am happy to answer more questions... 

I would probably create a new view within Application designer that does all the joins/ unions, then add that view to the Query Tree security, then write the PSQuery using the new view.

I use Composite Query and Tom is absolutely correct.  I created a very large one that would always time out on me.  However, as of PeopleTools 8.60, you can now schedule Composite Query:

In PeopleTools 8.60, PeopleSoft Query introduces the Composite Query Scheduler, which is used to schedule a composite query to run in the PeopleSoft Process Scheduler. 

You can search for an existing run control ID or create a new run control ID and schedule a composite query using the Schedule Composite Query page, check the status of your request using the Process Monitor link, and view your output using the Report Manager link.

I can now schedule my Composite Query and not have to worry about it timing out.  So if you run into that issue, you have this option available.

Hey Audrey - As an alternative to using a Union, or creating a new view, you could also use the Composite Query tool.  This tool allows you to create a single data source, pulled from other 'base' queries built in Query Manager.  It accomplishes many of the things you need to do with a Union, and (in my experience) it runs pretty quickly.

Here are a couple of notes on Composite Query.

1. Navigation = Main Menu> Reporting Tools> Composite Query> Composite Query Manager.
2. You will need to have security for this functionality - we  used the delivered Roles Composite Query Developer or Composite Query User. 
3. The learning curve for Composite query is not too steep.  There are a set of little things to know, but those are documented in a variety of presentations and PeopleBooks.
   1. The biggest difference for me is that the regular, 'base' queries cannot have prompts.  You add the prompts at the Composite Query level.  
4. I like to think of Composite Queries as data sources, similar to what we build with BI Publisher. 

Good Luck, and I am happy to answer more questions... 

I would probably create a new view within Application designer that does all the joins/ unions, then add that view to the Query Tree security, then write the PSQuery using the new view.

Tom, 

I have wondered if composite query would work for this. What my query does is pulls all positions. The top level of the query pulls all filled positions, and gives quite a bit of associated data: dept, business unit, emplid, name, pay band, midpoint, pay rate, position start date, etc. The bottom level pulls the same information but for vacant positions. I use expressions for the fields that aren't applicable such as emplid, and name. Do you think this would be possible with composite query?

Thanks!

Hey Audrey - As an alternative to using a Union, or creating a new view, you could also use the Composite Query tool.  This tool allows you to create a single data source, pulled from other 'base' queries built in Query Manager.  It accomplishes many of the things you need to do with a Union, and (in my experience) it runs pretty quickly.

Here are a couple of notes on Composite Query.

1. Navigation = Main Menu> Reporting Tools> Composite Query> Composite Query Manager.
2. You will need to have security for this functionality - we  used the delivered Roles Composite Query Developer or Composite Query User. 
3. The learning curve for Composite query is not too steep.  There are a set of little things to know, but those are documented in a variety of presentations and PeopleBooks.
   1. The biggest difference for me is that the regular, 'base' queries cannot have prompts.  You add the prompts at the Composite Query level.  
4. I like to think of Composite Queries as data sources, similar to what we build with BI Publisher. 

Good Luck, and I am happy to answer more questions... 

I would probably create a new view within Application designer that does all the joins/ unions, then add that view to the Query Tree security, then write the PSQuery using the new view.

Hey Audrey

I think it would work.  Since it sounds like both your queries pull in distinct data sets, what I would do is the following:

1. In Query Manager, create a 'base ' query that encompasses all students that might get pulled in by your Source 1 and Source 2.  This might be a simple query of all active students in ACAD_PROG, or a list of emplids from STUDENT_AID, or whatever.  The point would be to have a query that defines your base group that all your possible students would exist in.  All student returned by queries in Step 2 and 3 should exist in this base query.
2. In Query Manager, create a 'Source 1' query that is the same as your (existing) upper level query you described. 
3. In Query Manager, create a 'Source 2' query that is the same as the (existing) lower level query. 
4. You will now have 3 queries total - Source Base, Source 1 and Source 2. 
5. In Composite Query, start by adding Source Base query.  (You will be adding the other two queries to this 'base'.)
6. Add the Source 1 Query, as a standard join to Source Base query
   1. Pull in the fields you need, arrange them in the order you want, etc. 
7. Add the Source 2 query, as a standard join to the Source Base query
   1. Pull in the fields you need, arrange them in the order you want, etc. 
8. Add aggregate functions, rename fields, add expressions as needed. 

I think as a starting strategy, this will work.  Of course, the devil is in the details, so it will probably take a little fiddling around with the data and the various queries to find out what works. 

If you end up going down the Composite Query road, let us know how it goes!  Good Luck, and don't hesitate to reach out.

:-) 

Tom, 

I have wondered if composite query would work for this. What my query does is pulls all positions. The top level of the query pulls all filled positions, and gives quite a bit of associated data: dept, business unit, emplid, name, pay band, midpoint, pay rate, position start date, etc. The bottom level pulls the same information but for vacant positions. I use expressions for the fields that aren't applicable such as emplid, and name. Do you think this would be possible with composite query?

Thanks!

Hey Audrey - As an alternative to using a Union, or creating a new view, you could also use the Composite Query tool.  This tool allows you to create a single data source, pulled from other 'base' queries built in Query Manager.  It accomplishes many of the things you need to do with a Union, and (in my experience) it runs pretty quickly.

Here are a couple of notes on Composite Query.

1. Navigation = Main Menu> Reporting Tools> Composite Query> Composite Query Manager.
2. You will need to have security for this functionality - we  used the delivered Roles Composite Query Developer or Composite Query User. 
3. The learning curve for Composite query is not too steep.  There are a set of little things to know, but those are documented in a variety of presentations and PeopleBooks.
   1. The biggest difference for me is that the regular, 'base' queries cannot have prompts.  You add the prompts at the Composite Query level.  
4. I like to think of Composite Queries as data sources, similar to what we build with BI Publisher. 

Good Luck, and I am happy to answer more questions... 

I would probably create a new view within Application designer that does all the joins/ unions, then add that view to the Query Tree security, then write the PSQuery using the new view.

I want to add this to Tom's directions.  You will need to create these queries without prompts before using Composite Query.  The prompts will need to be created within Composite Query itself.

Hey Audrey

I think it would work.  Since it sounds like both your queries pull in distinct data sets, what I would do is the following:

1. In Query Manager, create a 'base ' query that encompasses all students that might get pulled in by your Source 1 and Source 2.  This might be a simple query of all active students in ACAD_PROG, or a list of emplids from STUDENT_AID, or whatever.  The point would be to have a query that defines your base group that all your possible students would exist in.  All student returned by queries in Step 2 and 3 should exist in this base query.
2. In Query Manager, create a 'Source 1' query that is the same as your (existing) upper level query you described. 
3. In Query Manager, create a 'Source 2' query that is the same as the (existing) lower level query. 
4. You will now have 3 queries total - Source Base, Source 1 and Source 2. 
5. In Composite Query, start by adding Source Base query.  (You will be adding the other two queries to this 'base'.)
6. Add the Source 1 Query, as a standard join to Source Base query
   1. Pull in the fields you need, arrange them in the order you want, etc. 
7. Add the Source 2 query, as a standard join to the Source Base query
   1. Pull in the fields you need, arrange them in the order you want, etc. 
8. Add aggregate functions, rename fields, add expressions as needed. 

I think as a starting strategy, this will work.  Of course, the devil is in the details, so it will probably take a little fiddling around with the data and the various queries to find out what works. 

If you end up going down the Composite Query road, let us know how it goes!  Good Luck, and don't hesitate to reach out.

:-) 

Tom, 

I have wondered if composite query would work for this. What my query does is pulls all positions. The top level of the query pulls all filled positions, and gives quite a bit of associated data: dept, business unit, emplid, name, pay band, midpoint, pay rate, position start date, etc. The bottom level pulls the same information but for vacant positions. I use expressions for the fields that aren't applicable such as emplid, and name. Do you think this would be possible with composite query?

Thanks!

Hey Audrey - As an alternative to using a Union, or creating a new view, you could also use the Composite Query tool.  This tool allows you to create a single data source, pulled from other 'base' queries built in Query Manager.  It accomplishes many of the things you need to do with a Union, and (in my experience) it runs pretty quickly.

Here are a couple of notes on Composite Query.

1. Navigation = Main Menu> Reporting Tools> Composite Query> Composite Query Manager.
2. You will need to have security for this functionality - we  used the delivered Roles Composite Query Developer or Composite Query User. 
3. The learning curve for Composite query is not too steep.  There are a set of little things to know, but those are documented in a variety of presentations and PeopleBooks.
   1. The biggest difference for me is that the regular, 'base' queries cannot have prompts.  You add the prompts at the Composite Query level.  
4. I like to think of Composite Queries as data sources, similar to what we build with BI Publisher. 

Good Luck, and I am happy to answer more questions... 

I would probably create a new view within Application designer that does all the joins/ unions, then add that view to the Query Tree security, then write the PSQuery using the new view.