PS Query & SQL

Good morning! 

We have started working on query clean up as our numbers have gotten a little ridiculous and we have a lot of queries that do very similar things to one another. 

One challenge to this is communicating to users that a query is going away and that they should start using a new or different query instead. I want to show what I came up with last week to help us do this. For those queries we will be deleting,  I did two things.

1. I changed the description field to show that that a query is going to be deleted.

2. When the user clicks on the query to run it, I created a prompt that gives more details, including the query they should start using instead. 

For each of these queries, I also added criteria that will make the query return 0 results if they do click to run the query. For example, for most of these queries, I put criteria where Run Date = 1/1/25, which doesn't exist. 

We'll leave these queries up for a couple of weeks, hopefully giving our users enough time to take note of this change. 

Just thought I'd share in case someone finds this useful. I'd also love to hear how you manage these changes to queries.

That is pretty slick, Audry and a lot of work.  We have so many queries at UNLV (most that I created over many years) that we are attempting to do the same.  I like what you did with the prompt.  You changed the heading text to "**Notice**" and the default value to include your message.  I love seeing things in CS being repurposed.

Good morning! 

We have started working on query clean up as our numbers have gotten a little ridiculous and we have a lot of queries that do very similar things to one another. 

One challenge to this is communicating to users that a query is going away and that they should start using a new or different query instead. I want to show what I came up with last week to help us do this. For those queries we will be deleting,  I did two things.

1. I changed the description field to show that that a query is going to be deleted.

2. When the user clicks on the query to run it, I created a prompt that gives more details, including the query they should start using instead. 

For each of these queries, I also added criteria that will make the query return 0 results if they do click to run the query. For example, for most of these queries, I put criteria where Run Date = 1/1/25, which doesn't exist. 

We'll leave these queries up for a couple of weeks, hopefully giving our users enough time to take note of this change. 

Just thought I'd share in case someone finds this useful. I'd also love to hear how you manage these changes to queries.

Good Morning Audrey - What a fantastic idea!  Like Dan, I love seeing innovative ways folks use the tools in our collective toolbox.  Very cool indeed! 

I have a related question - our institution has been on Campus Solutions for 20+ years now, so we have a lot of queries built up.  How did your institution determine which queries to delete?  Do you have some way to track query usage?  Or did you use some other method? 

As a note, we have (sadly) turned off Query Stats and cannot use that tool to guide our choices. 

Thank you, and great work!

That is pretty slick, Audry and a lot of work.  We have so many queries at UNLV (most that I created over many years) that we are attempting to do the same.  I like what you did with the prompt.  You changed the heading text to "**Notice**" and the default value to include your message.  I love seeing things in CS being repurposed.

Good morning! 

We have started working on query clean up as our numbers have gotten a little ridiculous and we have a lot of queries that do very similar things to one another. 

One challenge to this is communicating to users that a query is going away and that they should start using a new or different query instead. I want to show what I came up with last week to help us do this. For those queries we will be deleting,  I did two things.

1. I changed the description field to show that that a query is going to be deleted.

2. When the user clicks on the query to run it, I created a prompt that gives more details, including the query they should start using instead. 

For each of these queries, I also added criteria that will make the query return 0 results if they do click to run the query. For example, for most of these queries, I put criteria where Run Date = 1/1/25, which doesn't exist. 

We'll leave these queries up for a couple of weeks, hopefully giving our users enough time to take note of this change. 

Just thought I'd share in case someone finds this useful. I'd also love to hear how you manage these changes to queries.

Good Morning Audrey - What a fantastic idea!  Like Dan, I love seeing innovative ways folks use the tools in our collective toolbox.  Very cool indeed! 

I have a related question - our institution has been on Campus Solutions for 20+ years now, so we have a lot of queries built up.  How did your institution determine which queries to delete?  Do you have some way to track query usage?  Or did you use some other method? 

As a note, we have (sadly) turned off Query Stats and cannot use that tool to guide our choices. 

Thank you, and great work!

That is pretty slick, Audry and a lot of work.  We have so many queries at UNLV (most that I created over many years) that we are attempting to do the same.  I like what you did with the prompt.  You changed the heading text to "**Notice**" and the default value to include your message.  I love seeing things in CS being repurposed.

Good morning! 

We have started working on query clean up as our numbers have gotten a little ridiculous and we have a lot of queries that do very similar things to one another. 

One challenge to this is communicating to users that a query is going away and that they should start using a new or different query instead. I want to show what I came up with last week to help us do this. For those queries we will be deleting,  I did two things.

1. I changed the description field to show that that a query is going to be deleted.

2. When the user clicks on the query to run it, I created a prompt that gives more details, including the query they should start using instead. 

For each of these queries, I also added criteria that will make the query return 0 results if they do click to run the query. For example, for most of these queries, I put criteria where Run Date = 1/1/25, which doesn't exist. 

We'll leave these queries up for a couple of weeks, hopefully giving our users enough time to take note of this change. 

Just thought I'd share in case someone finds this useful. I'd also love to hear how you manage these changes to queries.

I used the LASTUPDDTTM /CREATEDTTM fields in PSQRYDEFN to give me an Idea on when it was created and filtered out all the PPLSOFT queries.  For us, the USER is blank if it is public but I saw some differences in how people did that if I remember right during my research.  From that list, based on Query Naming convention, I sorted them in excel and sent them to the departments for review.  

I am 1000% stealing this prompt Idea.

Good Morning Audrey - What a fantastic idea!  Like Dan, I love seeing innovative ways folks use the tools in our collective toolbox.  Very cool indeed! 

I have a related question - our institution has been on Campus Solutions for 20+ years now, so we have a lot of queries built up.  How did your institution determine which queries to delete?  Do you have some way to track query usage?  Or did you use some other method? 

As a note, we have (sadly) turned off Query Stats and cannot use that tool to guide our choices. 

Thank you, and great work!

That is pretty slick, Audry and a lot of work.  We have so many queries at UNLV (most that I created over many years) that we are attempting to do the same.  I like what you did with the prompt.  You changed the heading text to "**Notice**" and the default value to include your message.  I love seeing things in CS being repurposed.

Good morning! 

We have started working on query clean up as our numbers have gotten a little ridiculous and we have a lot of queries that do very similar things to one another. 

One challenge to this is communicating to users that a query is going away and that they should start using a new or different query instead. I want to show what I came up with last week to help us do this. For those queries we will be deleting,  I did two things.

1. I changed the description field to show that that a query is going to be deleted.

2. When the user clicks on the query to run it, I created a prompt that gives more details, including the query they should start using instead. 

For each of these queries, I also added criteria that will make the query return 0 results if they do click to run the query. For example, for most of these queries, I put criteria where Run Date = 1/1/25, which doesn't exist. 

We'll leave these queries up for a couple of weeks, hopefully giving our users enough time to take note of this change. 

Just thought I'd share in case someone finds this useful. I'd also love to hear how you manage these changes to queries.

That is pretty slick, Audry and a lot of work.  We have so many queries at UNLV (most that I created over many years) that we are attempting to do the same.  I like what you did with the prompt.  You changed the heading text to "**Notice**" and the default value to include your message.  I love seeing things in CS being repurposed.

Good morning! 

We have started working on query clean up as our numbers have gotten a little ridiculous and we have a lot of queries that do very similar things to one another. 

One challenge to this is communicating to users that a query is going away and that they should start using a new or different query instead. I want to show what I came up with last week to help us do this. For those queries we will be deleting,  I did two things.

1. I changed the description field to show that that a query is going to be deleted.

2. When the user clicks on the query to run it, I created a prompt that gives more details, including the query they should start using instead. 

For each of these queries, I also added criteria that will make the query return 0 results if they do click to run the query. For example, for most of these queries, I put criteria where Run Date = 1/1/25, which doesn't exist. 

We'll leave these queries up for a couple of weeks, hopefully giving our users enough time to take note of this change. 

Just thought I'd share in case someone finds this useful. I'd also love to hear how you manage these changes to queries.

That's very cool!  We also have tons of queries including multiples of things that are very similar and haven't really come across good solutions to manage that.  That said, our team has a process where we run a query stats query once a year, that shows the last time any public queries were run, how often they're used, etc. We share the list of anything that hasn't been run recently (last time we did things that hadn't been run in five years) with our group, and then anything that isn't requested to be kept is deleted.  

That is pretty slick, Audry and a lot of work.  We have so many queries at UNLV (most that I created over many years) that we are attempting to do the same.  I like what you did with the prompt.  You changed the heading text to "**Notice**" and the default value to include your message.  I love seeing things in CS being repurposed.

Good morning! 

We have started working on query clean up as our numbers have gotten a little ridiculous and we have a lot of queries that do very similar things to one another. 

One challenge to this is communicating to users that a query is going away and that they should start using a new or different query instead. I want to show what I came up with last week to help us do this. For those queries we will be deleting,  I did two things.

1. I changed the description field to show that that a query is going to be deleted.

2. When the user clicks on the query to run it, I created a prompt that gives more details, including the query they should start using instead. 

For each of these queries, I also added criteria that will make the query return 0 results if they do click to run the query. For example, for most of these queries, I put criteria where Run Date = 1/1/25, which doesn't exist. 

We'll leave these queries up for a couple of weeks, hopefully giving our users enough time to take note of this change. 

Just thought I'd share in case someone finds this useful. I'd also love to hear how you manage these changes to queries.

Good morning! 

We have started working on query clean up as our numbers have gotten a little ridiculous and we have a lot of queries that do very similar things to one another. 

One challenge to this is communicating to users that a query is going away and that they should start using a new or different query instead. I want to show what I came up with last week to help us do this. For those queries we will be deleting,  I did two things.

1. I changed the description field to show that that a query is going to be deleted.

2. When the user clicks on the query to run it, I created a prompt that gives more details, including the query they should start using instead. 

For each of these queries, I also added criteria that will make the query return 0 results if they do click to run the query. For example, for most of these queries, I put criteria where Run Date = 1/1/25, which doesn't exist. 

We'll leave these queries up for a couple of weeks, hopefully giving our users enough time to take note of this change. 

Just thought I'd share in case someone finds this useful. I'd also love to hear how you manage these changes to queries.

Good morning! 

We have started working on query clean up as our numbers have gotten a little ridiculous and we have a lot of queries that do very similar things to one another. 

One challenge to this is communicating to users that a query is going away and that they should start using a new or different query instead. I want to show what I came up with last week to help us do this. For those queries we will be deleting,  I did two things.

1. I changed the description field to show that that a query is going to be deleted.

2. When the user clicks on the query to run it, I created a prompt that gives more details, including the query they should start using instead. 

For each of these queries, I also added criteria that will make the query return 0 results if they do click to run the query. For example, for most of these queries, I put criteria where Run Date = 1/1/25, which doesn't exist. 

We'll leave these queries up for a couple of weeks, hopefully giving our users enough time to take note of this change. 

Just thought I'd share in case someone finds this useful. I'd also love to hear how you manage these changes to queries.

Good morning! 

We have started working on query clean up as our numbers have gotten a little ridiculous and we have a lot of queries that do very similar things to one another. 

One challenge to this is communicating to users that a query is going away and that they should start using a new or different query instead. I want to show what I came up with last week to help us do this. For those queries we will be deleting,  I did two things.

1. I changed the description field to show that that a query is going to be deleted.

2. When the user clicks on the query to run it, I created a prompt that gives more details, including the query they should start using instead. 

For each of these queries, I also added criteria that will make the query return 0 results if they do click to run the query. For example, for most of these queries, I put criteria where Run Date = 1/1/25, which doesn't exist. 

We'll leave these queries up for a couple of weeks, hopefully giving our users enough time to take note of this change. 

Just thought I'd share in case someone finds this useful. I'd also love to hear how you manage these changes to queries.

Thanks Audrey, I like your creativity. It seems like you've hit a common struggle for many of us - how to keep control of an ever-expanding inventory of queries. One tool I think would help greatly with this quest is to have an improved Query Statistics, improved so that it can run continuously in production environments without affecting performance. I've created an idea in the Oracle PeopleSoft Ideas Lab. I would appreciate more interest in that, and perhaps we can convince Oracle to make that enhancement:

https://community.oracle.com/mosc/discussion/4527117/query-statistics-improve-performance-by-adding-ability-to-exclude-selected-queries

Good morning! 

We have started working on query clean up as our numbers have gotten a little ridiculous and we have a lot of queries that do very similar things to one another. 

One challenge to this is communicating to users that a query is going away and that they should start using a new or different query instead. I want to show what I came up with last week to help us do this. For those queries we will be deleting,  I did two things.

1. I changed the description field to show that that a query is going to be deleted.

2. When the user clicks on the query to run it, I created a prompt that gives more details, including the query they should start using instead. 

For each of these queries, I also added criteria that will make the query return 0 results if they do click to run the query. For example, for most of these queries, I put criteria where Run Date = 1/1/25, which doesn't exist. 

We'll leave these queries up for a couple of weeks, hopefully giving our users enough time to take note of this change. 

Just thought I'd share in case someone finds this useful. I'd also love to hear how you manage these changes to queries.

Thanks all, for your responses. There are so many great ideas here. 

Here's a little more background on our project. We are just starting, so I don't have all the answers yet!

The State of NM currently supports around 23K employees, and we have approx 2400 non PS delivered queries. Of those, 950 are public queries, and that is our focus (we are not touching PS delivered queries at all--that's another topic). I do recommend to anyone who can support it, to turn on query stats. It is extremely helpful for cleanup like this. 

The steps that we've taken so far are:

1. take a backup of all queries. That way we have a starting point, and if we accidentally delete something we shouldn't have, we can restore it.
2.  Using query stats, delete all queries that either have not been run in the last 2 years, or were created 2 or more years ago and have not been run at all.
3. Work to identify like queries. Sometimes this is easy, other times, it is not. For the example I used in the screenshots in my original post, I was focused on all queries that use HR_ACCTG_LINE. Once I identified all the queries, I went through them to see how they were different from one another. By and large, the difference was in the prompt, not in the field output. I then made a new query that utilized all the different prompts, using "like" and a default prompt value of '%' so that the prompt was essentially rendered optional. The next challenge for us is tackling large groups of queries that utilize records like Job. For these, we are going to have to go one by one and start grouping them into what kind of data they are pulling.
4. As we are analyzing these queries, we also started standardizing the use of the LONGDESCR field on PSQUERYDEFN (screenshot below). We are putting 5 notes in the field: 1. Purpose of the query, 2. the prompts that are on the query and if there's anything special about them 3. whether or not the query needs to be scheduled 4. Any other notes that our team thinks is relevant (mostly just for our own team's use/knowledge) 5. Change log. Our goal is to create a query that queries the PSQUERYDEFN and shows users all the queries and the purpose behind them. We will probably have to build a view to do this as we found we cannot do a substring on a text field--we need to figure this part out.

Here are some additional planned steps:

1. With the stats in place, we can see what queries are not being run often, and look to chop those off or redirect employees to another similar query.
2. We have queries that are not named using our newer naming convention. In the name, we now always work to identify what kind of query it is. For example, for all of our queries for public consumption, we start them off with NMS after that, we identify the area. If it's payroll, it is NMS_PY, if it's benefits, it's NMS_BN. For queries not fitting this convention, they will be deleted or renamed.
3. We also have queries not intended for general consumption. For example, queries meant specifically for the payroll team. We have a different naming convention for those. Since we can easily identify those, we are looking to put those queries into a specific query security group that would them allow only our payroll team, benefits team, etc. to see those types of queries--right now, they are available to all.
4. I really love Steph's idea of putting queries considered for deletion into a folder and telling users they have a certain amount of time to let us know if they really need those queries. I think we will utilize something similar

I know this project will take us a while, but hopefully will result in much cleaner queries and less query creation going forward.

Thanks Audrey, I like your creativity. It seems like you've hit a common struggle for many of us - how to keep control of an ever-expanding inventory of queries. One tool I think would help greatly with this quest is to have an improved Query Statistics, improved so that it can run continuously in production environments without affecting performance. I've created an idea in the Oracle PeopleSoft Ideas Lab. I would appreciate more interest in that, and perhaps we can convince Oracle to make that enhancement:

https://community.oracle.com/mosc/discussion/4527117/query-statistics-improve-performance-by-adding-ability-to-exclude-selected-queries

Good morning! 

We have started working on query clean up as our numbers have gotten a little ridiculous and we have a lot of queries that do very similar things to one another. 

One challenge to this is communicating to users that a query is going away and that they should start using a new or different query instead. I want to show what I came up with last week to help us do this. For those queries we will be deleting,  I did two things.

1. I changed the description field to show that that a query is going to be deleted.

2. When the user clicks on the query to run it, I created a prompt that gives more details, including the query they should start using instead. 

For each of these queries, I also added criteria that will make the query return 0 results if they do click to run the query. For example, for most of these queries, I put criteria where Run Date = 1/1/25, which doesn't exist. 

We'll leave these queries up for a couple of weeks, hopefully giving our users enough time to take note of this change. 

Just thought I'd share in case someone finds this useful. I'd also love to hear how you manage these changes to queries.

Hi Audrey,

Thanks for sharing your work in this area, it is clearly of interest to a lot of us. I like many of the ideas you have brought up.  I wanted to share that you can use expressions in PS query to get the metadata out of the DESCRLONG field in PSQUERYDEFN.  The field does need to be covered from a text field to a character field to be used in PS query.  Below are examples using your screenshot on how to pull each section of DESCRLONG field.  Most commonly I pull the whole field using:

TO_CHAR(SUBSTR(A.DESCRLONG,1,2000))

That expression gets you the first 2,000 characters of that text field which usually covers my needs.

Assumptions:

• PSQRYDEFN alias is A
• All the numbers are in order and are in the same format (number,period, space) 
• You want to include the label text(Prompts: ...)
• FYI, the adding and subtracting 3 is to exclude the number

Purpose

SUBSTR(TO_CHAR(A.DESCRLONG),3,INSTR(TO_CHAR(A.DESCRLONG),'2. ',1,1)-3) 

Prompts
SUBSTR(TO_CHAR(A.DESCRLONG),INSTR(TO_CHAR(A.DESCRLONG),'2. ',1,1)+3,(INSTR(TO_CHAR(A.DESCRLONG),'3. ',1,1)-1) - (INSTR(TO_CHAR(A.DESCRLONG),'2. ',1,1)+3)) 

scheduled 
SUBSTR(TO_CHAR(A.DESCRLONG),INSTR(TO_CHAR(A.DESCRLONG),'3. ',1,1)+3,(INSTR(TO_CHAR(A.DESCRLONG),'4. ',1,1)-1) - (INSTR(TO_CHAR(A.DESCRLONG),'3. ',1,1)+3)) 

other notes 
SUBSTR(TO_CHAR(A.DESCRLONG),INSTR(TO_CHAR(A.DESCRLONG),'4. ',1,1)+3,(INSTR(TO_CHAR(A.DESCRLONG),'5. ',1,1)-1) - (INSTR(TO_CHAR(A.DESCRLONG),'4. ',1,1)+3)) 
Change log
SUBSTR(TO_CHAR(A.DESCRLONG),INSTR(TO_CHAR(A.DESCRLONG),'5. ',1,1)+3) 

Other metadata records I commonly use:

PSQRYBIND - Query Prompts

PSQRYRECORD - Query Records 

PSQRYEXPR - Query Expression

Thanks all, for your responses. There are so many great ideas here. 

Here's a little more background on our project. We are just starting, so I don't have all the answers yet!

The State of NM currently supports around 23K employees, and we have approx 2400 non PS delivered queries. Of those, 950 are public queries, and that is our focus (we are not touching PS delivered queries at all--that's another topic). I do recommend to anyone who can support it, to turn on query stats. It is extremely helpful for cleanup like this. 

The steps that we've taken so far are:

1. take a backup of all queries. That way we have a starting point, and if we accidentally delete something we shouldn't have, we can restore it.
2.  Using query stats, delete all queries that either have not been run in the last 2 years, or were created 2 or more years ago and have not been run at all.
3. Work to identify like queries. Sometimes this is easy, other times, it is not. For the example I used in the screenshots in my original post, I was focused on all queries that use HR_ACCTG_LINE. Once I identified all the queries, I went through them to see how they were different from one another. By and large, the difference was in the prompt, not in the field output. I then made a new query that utilized all the different prompts, using "like" and a default prompt value of '%' so that the prompt was essentially rendered optional. The next challenge for us is tackling large groups of queries that utilize records like Job. For these, we are going to have to go one by one and start grouping them into what kind of data they are pulling.
4. As we are analyzing these queries, we also started standardizing the use of the LONGDESCR field on PSQUERYDEFN (screenshot below). We are putting 5 notes in the field: 1. Purpose of the query, 2. the prompts that are on the query and if there's anything special about them 3. whether or not the query needs to be scheduled 4. Any other notes that our team thinks is relevant (mostly just for our own team's use/knowledge) 5. Change log. Our goal is to create a query that queries the PSQUERYDEFN and shows users all the queries and the purpose behind them. We will probably have to build a view to do this as we found we cannot do a substring on a text field--we need to figure this part out.

Here are some additional planned steps:

1. With the stats in place, we can see what queries are not being run often, and look to chop those off or redirect employees to another similar query.
2. We have queries that are not named using our newer naming convention. In the name, we now always work to identify what kind of query it is. For example, for all of our queries for public consumption, we start them off with NMS after that, we identify the area. If it's payroll, it is NMS_PY, if it's benefits, it's NMS_BN. For queries not fitting this convention, they will be deleted or renamed.
3. We also have queries not intended for general consumption. For example, queries meant specifically for the payroll team. We have a different naming convention for those. Since we can easily identify those, we are looking to put those queries into a specific query security group that would them allow only our payroll team, benefits team, etc. to see those types of queries--right now, they are available to all.
4. I really love Steph's idea of putting queries considered for deletion into a folder and telling users they have a certain amount of time to let us know if they really need those queries. I think we will utilize something similar

I know this project will take us a while, but hopefully will result in much cleaner queries and less query creation going forward.

Thanks Audrey, I like your creativity. It seems like you've hit a common struggle for many of us - how to keep control of an ever-expanding inventory of queries. One tool I think would help greatly with this quest is to have an improved Query Statistics, improved so that it can run continuously in production environments without affecting performance. I've created an idea in the Oracle PeopleSoft Ideas Lab. I would appreciate more interest in that, and perhaps we can convince Oracle to make that enhancement:

https://community.oracle.com/mosc/discussion/4527117/query-statistics-improve-performance-by-adding-ability-to-exclude-selected-queries

Good morning! 

We have started working on query clean up as our numbers have gotten a little ridiculous and we have a lot of queries that do very similar things to one another. 

One challenge to this is communicating to users that a query is going away and that they should start using a new or different query instead. I want to show what I came up with last week to help us do this. For those queries we will be deleting,  I did two things.

1. I changed the description field to show that that a query is going to be deleted.

2. When the user clicks on the query to run it, I created a prompt that gives more details, including the query they should start using instead. 

For each of these queries, I also added criteria that will make the query return 0 results if they do click to run the query. For example, for most of these queries, I put criteria where Run Date = 1/1/25, which doesn't exist. 

We'll leave these queries up for a couple of weeks, hopefully giving our users enough time to take note of this change. 

Just thought I'd share in case someone finds this useful. I'd also love to hear how you manage these changes to queries.