Find existing queries that we can use is our goal.
Do you know how to find an existing similar query before creating a new one from scratch? Doing the research before creating a new one is a MUST approach that you should remember and always to do first. The SQL text of PS query is not stored as it is in system, but is generated when you click on the tab of View SQL in Query Manager. If you have access privilege in Query Manager, you can find all detail information about how the query is constructed. Although you may have privilege to view the public query, it is still very time consuming to go through each query to figure out which query that you can use, enhance or modify. Here are some tips and tricks that can help you find some queries that you want to see.
First thing you need to check: What is the output RECORD.FIELD of the query will return?
You must know the end result in terms of RECORD.FIELD. The end result in PS query is related to the RECORD.FIELD in TABLE.COLUMN in database tables. How can you find what the RECORD.FIELD are that the query will return? You can use the metadata PSQryField to find all RECORD.FIELDs that will be returned. The current existing query may return some of the RECORD.FIELDs which are interesting to you but may not include every RECORD.FIELDs that you want to see. The best scenario is that you find many of them which you want to get, so you can be pretty sure that the existing query can help you in some way during your research. This type of query is most likely what you can use directly or indirectly without creating a new one from scratch.
In this query below, we create a query allowing the user to do “List search” by specifying multiple RECORD.FIELD pairs separated by COMMA, then the query will find all potential queries that match any one of those RECORD.FIELD pairs. The user can start to investigate and may find something useful.
Without the nice query to find the useful existing queries, you still can try to find some by searching the metadata table PSQryField by yourself.
What are the records used in query to join?
Without knowing the exact RECORD.FIELD, you must know which RECORD are interesting to you at least. Do you know how to find an existing useful query by searching the records used in the query? The records that are used in query join represents some meaningful information associated with the output fields of the query. In this query example below, we let the user find all queries involved with those records. The more records used in query join means that the more specific type of information between records will be associated to each other. These types of information representing the PARENT-CHILD relationship between records. The PARENT-CHILD relationship will be joined together by specifying the PARENT-CHILD keys in query that is another kind of clues that we can use to search too. You can use the metadata PSQryRecord and PSQrySelect to find all records used and how they are joined together in query. From the example below, we can find more than 1000 LEDGER related queries were created by our university. Although the number is still too high to us, we have some idea to start with now at least.
What are the fields of record used in query to join?
The PARENT-CHILD keys are the RECORD.FIELDs representing the PARENT-CHILD relationship between database tables. Do you know how to find an existing similar query by searching the fields used in query? We need to specify at least one record in query and most of the time we need two records to join together in order to find the needed information from one PARENT record to the other CHILD relationship that represents the data we are searching for. In query join, the same meaningful field in both records are used to join together to find the PARENT-CHILD relation back from these two records, so we can limit the number of rows and only related rows should be returned from two records. This is the basic concept that the relational database ORACLE is designed. These PARENT-CHILD keys are extremely important in finding the existing queries that you need. You can use the metadata PSQryCriteria to find all the criteria used in query. In general, all the primary keys in parent record will be used to join with the corresponding keys of the child record. The name of these keys can be same or different, but the data underneath must be the same.
We have some customization here again. There is a good amount of work that needs to be done to make this raw data searchable and here is just some of the customized code showing the high level of design. For the users, they just use this searching query and they will find all the potential queries that they want to find. The idea of this searching query is allowing the user to specify up to 5 RECORD.FIELDs join keys used in query criteria of PS query.
What are the short and long description of the query?
After we have done all the searching above, we still can’t find any existing useful query. We can try something else below. Do you know how to find an existing similar query by searching the short or long description? The short and long description of query are stored in different fields that represent some meaningful information about the query. This is the easiest way to try to find some query but it is the most unreliable approach to find what you want, because the description is hard to predict in your searching. You can use the metadata PSQryDefn to find short and long description of the query.
Don’t forget about searching PS query expression too.
Here is another area that you can search but you may forget. Do you know how to find an existing similar query by searching the query expression in query? The query expression in PS query is a free form that allows developer to create some code used in PS query. The RECORD.FIELD name can be used in expression, so you need to search there too. Because of how the expression is saved in the database, we need to have some interpreter translating the PS Query expression data to a new form, so that the user can search the RECORD.FIELD from our searching query. The code below shows our customized solution on how we have that translation done in a view.
There is a new function SYSADM.UNC_ExpField_Decode( C.EXPRESSIONTEXT, C.FIELDLIST, C.FIELDLIST2) in code below, that will allow user to enter RECORD.FIELD searching string to query the tables, from metadata PSQRYEXPR A, PS_NC_EXP_FIELD C for matching the user input values. I can provide the code if you are interested to see how that code is written.
What’s the end result of your research?
What’s the end result of your research? It all depends on how good your search is utilized. The best case is finding one that can be used immediately. The second good choice is finding an existing one, then we can enhance the code by adding the PROMPTs, so that query can be used in multiple ways. The third scenario is finding one that can be modified and saved as a new query, so you don’t need to create a new one from scratch. The fourth case is researching more and more by specifying different searching criteria, so you may find something useful based on how much time and effort that you have spent, get some idea, and the worst case is creating a new one from scratch. The more you do the research, the better understanding of your system, and this process will help you in the long run. If you think that this is useful to your organization and we would love to share the code with you and good luck in your research.