PS Query & SQL

PSQuery is great for quick reporting, but sometimes you need full SQL to get the job done. When do you prefer PSQuery, and when do you switch to SQL? Do you use both together? Let's discuss use cases where one shines over the other!

My preference would be to forego PSQuery altogether, but unfortunately, that is not the world I live in.  SQL brings things like CTEs, inline views, ANSI joins (for everything) that PSQuery cannot emulate.  PSQuery allows you to do population selections via query, which is easier and faster than either creating views or using processes like SQRs or Equation Engine with callable SQL.  PSQuery in combination with BI Publisher can make for some dynamic reporting that brings advantages over straight SQL, but it really depends on your environment and the other reporting tools at your disposal.     

I do find both useful in scenarios where I'm making a change in Production, but want to try the updated query in one of our test instances.  I can take the SQL from PSQuery and place it in SQL Developer (with some minor adjustments) and try it against one of the test DBs.  It makes the process faster than either having to wait for the change in Prod to rollover to the Test instances the next day or making a change management request. 

PSQuery is great for quick reporting, but sometimes you need full SQL to get the job done. When do you prefer PSQuery, and when do you switch to SQL? Do you use both together? Let's discuss use cases where one shines over the other!

I agree with oy on all points, Michael.  

Often times I am able to find a table in Campus Solutions, via inspect element, that is not on my query tree. Now I won't know if that table is worth getting added to my Query Tree. I then go into SQL developer to see if it is worth getting. If I need the table structure for an equation engine callable SQL, I will then just make the SQL there and port it over with the limitations in mind (e.g. CTE does not work in callable SQL due to SQL Parm Miscount).

My preference would be to forego PSQuery altogether, but unfortunately, that is not the world I live in.  SQL brings things like CTEs, inline views, ANSI joins (for everything) that PSQuery cannot emulate.  PSQuery allows you to do population selections via query, which is easier and faster than either creating views or using processes like SQRs or Equation Engine with callable SQL.  PSQuery in combination with BI Publisher can make for some dynamic reporting that brings advantages over straight SQL, but it really depends on your environment and the other reporting tools at your disposal.     

I do find both useful in scenarios where I'm making a change in Production, but want to try the updated query in one of our test instances.  I can take the SQL from PSQuery and place it in SQL Developer (with some minor adjustments) and try it against one of the test DBs.  It makes the process faster than either having to wait for the change in Prod to rollover to the Test instances the next day or making a change management request. 

PSQuery is great for quick reporting, but sometimes you need full SQL to get the job done. When do you prefer PSQuery, and when do you switch to SQL? Do you use both together? Let's discuss use cases where one shines over the other!

I agree with both Michael and Daniel.  I mainly think of PS Query as creating a public query for staff consumption or operations.   For anything else, I use SQL developer so I am not limited by PS Query structure.  It is a lot quicker to test by commenting out parts of the code and including notes in comments for complicated clauses.  

I agree with oy on all points, Michael.  

Often times I am able to find a table in Campus Solutions, via inspect element, that is not on my query tree. Now I won't know if that table is worth getting added to my Query Tree. I then go into SQL developer to see if it is worth getting. If I need the table structure for an equation engine callable SQL, I will then just make the SQL there and port it over with the limitations in mind (e.g. CTE does not work in callable SQL due to SQL Parm Miscount).

My preference would be to forego PSQuery altogether, but unfortunately, that is not the world I live in.  SQL brings things like CTEs, inline views, ANSI joins (for everything) that PSQuery cannot emulate.  PSQuery allows you to do population selections via query, which is easier and faster than either creating views or using processes like SQRs or Equation Engine with callable SQL.  PSQuery in combination with BI Publisher can make for some dynamic reporting that brings advantages over straight SQL, but it really depends on your environment and the other reporting tools at your disposal.     

I do find both useful in scenarios where I'm making a change in Production, but want to try the updated query in one of our test instances.  I can take the SQL from PSQuery and place it in SQL Developer (with some minor adjustments) and try it against one of the test DBs.  It makes the process faster than either having to wait for the change in Prod to rollover to the Test instances the next day or making a change management request. 

PSQuery is great for quick reporting, but sometimes you need full SQL to get the job done. When do you prefer PSQuery, and when do you switch to SQL? Do you use both together? Let's discuss use cases where one shines over the other!

I use PS Query as much as possible and use SQL Developer to assist in similar manners described. The reasons I use PS Query:

• I teach as well as do. Any solution I find can be shared with others using PS Query at my institution
• PS Queries are the basis for many other things (Publisher, QBUs, Forms)
• Uses our security model

I agree with both Michael and Daniel.  I mainly think of PS Query as creating a public query for staff consumption or operations.   For anything else, I use SQL developer so I am not limited by PS Query structure.  It is a lot quicker to test by commenting out parts of the code and including notes in comments for complicated clauses.  

I agree with oy on all points, Michael.  

Often times I am able to find a table in Campus Solutions, via inspect element, that is not on my query tree. Now I won't know if that table is worth getting added to my Query Tree. I then go into SQL developer to see if it is worth getting. If I need the table structure for an equation engine callable SQL, I will then just make the SQL there and port it over with the limitations in mind (e.g. CTE does not work in callable SQL due to SQL Parm Miscount).

My preference would be to forego PSQuery altogether, but unfortunately, that is not the world I live in.  SQL brings things like CTEs, inline views, ANSI joins (for everything) that PSQuery cannot emulate.  PSQuery allows you to do population selections via query, which is easier and faster than either creating views or using processes like SQRs or Equation Engine with callable SQL.  PSQuery in combination with BI Publisher can make for some dynamic reporting that brings advantages over straight SQL, but it really depends on your environment and the other reporting tools at your disposal.     

I do find both useful in scenarios where I'm making a change in Production, but want to try the updated query in one of our test instances.  I can take the SQL from PSQuery and place it in SQL Developer (with some minor adjustments) and try it against one of the test DBs.  It makes the process faster than either having to wait for the change in Prod to rollover to the Test instances the next day or making a change management request. 

PSQuery is great for quick reporting, but sometimes you need full SQL to get the job done. When do you prefer PSQuery, and when do you switch to SQL? Do you use both together? Let's discuss use cases where one shines over the other!