Blogs

Do You Know PS Query Metadata Records

By Alan Hwang posted 11-03-2021 08:24 AM

  

DYK PS Query Metadata Record

Do You Know how the PS Metadata is related to PS Query? I would like to show you all the important Metadata records that you need to know about. Based on the following 7 records, you should be able to find all the private and public queries defined in PS Query.

  1. PSQryDefn :  Query definition table related to owner, status and etc. one entry per query.
  2. PSQrySelect : All the record field statistic counts of query. One entry for each union query.
  3. PSQryField : All the record field selected in query, the RECORD.FIELDs are defined in SELECT statement.
  4. PSQryRecord : All the records joined together with correlation name under FROM statement.
  5. PSQryCriteria : All the RECORD.FIELD criteria under WHERE statement, all information is encoded with numbers only.
  6. PSQryExpr : All the expression used in query and the record fields are encoded with number.
  7. PSQryBind : All the PROMPTs used in query.  
3 comments
95 views

Related Content

Permalink

Comments

Jesse Stumbris
11-04-2021 01:27 PM

QUERY_RUN_PARM is the record that stores your scheduled query information. So if you think you might need to periodically update your scheduled queries (to update a prompt or something), joining this record to the other PSQuery metadata records is one way you could do it.

SCCPS_RUN_PARM does essentially the same thing for run controls where PSQuery is the chosen pop selection (so basically every run control that's not a scheduled query). And from there you can link up your query to any run control record that uses pop select to give yourself a full view of the params in a given run control. I've implemented something like for my functional area (Financial Aid); I had considered doing a presentation on it at Alliance '22 but didn't have time to submit the proposal. I'd be happy to share more if anyone's interested.

Jason Gill
11-04-2021 01:06 PM

Related to this, I once had built a very complicated query, then realized I needed to use a slightly different view than the one I had started with. Unfortunately, it was one of the main records, and deleting it would have lost all the joined records and complicated criteria I had set up. So I used the metadata tables to do a hot-swap on the backend via SQL"

Change the recname in an existing query from the back end

Useful if you have a complicated query already built and need to just swap out a record without recreating the query.

update psqryrecord set recname = 'new_recname' where recname = 'old_recname' and qryname = 'query_name';

update psqryfield set recname = 'new_recname' where recname = 'old_recname' and qryname = 'query_name';

This will not disturb expressions and criteria where the table alias is used, so if OLD_RECNAME was A, and used in criteria like "A.DEPTID = '1234', doing this swap leaves all the references intact, assuming of course that the field appears in both OLD_RECNAME and NEW_RECNAME.

Anna Kourouniotis
11-03-2021 09:30 AM

Alan, thanks for posting about this. I use the Definition tables a lot now!