Query Definitions in PeopleCode - PeopleCode Friday #3

Just recently I've been working on a project which required me to dynamically create and manipulate PeopleSoft Query Definitions in PeopleCode.

I've always known the Query API Class existed but i've never really used it.  PeopleBooks, of course, is always my first place to look for documentation on how to use anything so went looking for Query Class methods, collections and properties and also to look for worked examples.  Unfortunately, whilst the reference material was adequate I found the worked examples confusing and with a few errors.  So, next port of call in my search was a general search in Google for "PeopleSoft Query API PeopleCode" looking for some other PeopleSoft enthusiast who had posted some working code. Allas!  Nothing.

So, after a bit of tinkering around I got everything I needed to work and thought it might be useful to write up a simple example that works.

Here goes.

Everything you need to know about Query classes you can find here http://docs.oracle.com/cd/E58500_01/pt854pbh1/eng/pt/tpcr/concept_UnderstandingQueryClasses-c07e19.html#topofpage

Watch out for the listed code examples... some of them don't work.

There are 5 basic steps to creating a simple Query Definition using PeopleCode.  You can create both public and private queries using the Query API. My example here will create a private against one the delivered PeopleTools tables present in all PeopleSoft systems of any version - PSPRCSRQST, and then set 2 sets of criteria.

1) Security.  Make sure the user that is ececuting the PeopleCode has permissions to the source record used in the construction of your Query. Use Query Access Manager for this.

2) In PeopleCode you need to create a Query Object from the %SESSION object http://docs.oracle.com/cd/E58500_01/pt854pbh1/eng/pt/tpcr/concept_UnderstandingSessionClass-c07b74.html#topofpage

/* Get a Query oject */
&apiQuery = %Session.getquery();

3) Name the query and use the Create method to create a new Query Definition.  NOTE: If the Query Definition already exists then it overwrites it when you save. The second parameter specifies False for a private Query or True for a Public one.

/* Set Query Name.  Cannot contain spaces*/
&qryName = "MY_QUERY";

/* 3. Create a new Query */
&apiQuery.Create(&qryName, False, %Query_Query, "My query description", "My query long description");

TIP: Major sections of much of my code has numbers embedded in the comment headers.  This is just to help me and others that might have to maintain my code see instantly the sequence of flow.  It can be useful in long blocks of code.

4. When you use Query Manager in PIA to create a Query the first thing you do is specify which record the Query is based on. No different here.  Use the AddQuerySelect and AddQueryRecord methods to do this.  Because a Query Definition may have multiple SELECT statements in say a UNION and each SELECT may have one or more RECORDS you need to set these structures up even though your Query only has one SELECT and one RECORD.

/* 4. Create Query Select object and add single record from source drop down 
****************************************************************************/
&apiQuerySelect = &apiQuery.AddQuerySelect();
&apiQueryRec = &apiQuerySelect.AddQueryRecord("PSPRCSRQST");

5. What you do next may depend on your requirements but for this example I'm just going to iterate through the RECORD collection of FIELDS and add each FIELD object to the output list of my Query.  In PIA this is the same as ticking each field in the Fields TAB,  The key Query API method that adds the fields if AddQuerySelectedField. There are 4 parameters to this method - Record Name, Record Alias, Field Name and Field Label).

Setting the ColumnNumber sets the sequence of the field in the output list and I'm Ordering by the Field in ordinal position 1 in a Descending order. (NOTE: OrderByDirection property requires the ASCII code for D (descending).  If you want Ascending don't use CODE("A").  Instead just leave the OrderByDirection blank or CODE("") ).

/* 5. Add all fields from Source Record  to Query Output list 
**************************************************************************/
&recSource = CreateRecord(Record.PSPRCSRQST);
For &i = 1 To &recSource.FieldCount
   
   &apiQueryField = &apiQuerySelect.AddQuerySelectedField(&apiQueryRec.name, &apiQueryRec.recordalias, &recSource.GetField(&i).Name, &recSource.GetField(&i).Label);
   
   /*Make the field a display field */
   &apiQueryField.ColumnNumber = &i;
   &apiQueryField.HeadingType = %Query_HdgRftShort;
   
   /* Set order by value is specified*/
   &apiQueryField.OrderByNumber = 1; /* sort column 1 */
   &apiQueryField.OrderByDirection = Code("D"); /*descending*/
   
End-For;

6. If you run the Query now it would work but of course may return a lot of rows.  So let's add some simple criteria by creating a QueryCriteria object and using the AddCriteria method.  The parameter of the AddCriteria method is a string label.  It has no meaning and is simply a useful reference for the developer.

Once you have a handle on a QueryCriteria object it's really straight forward to specify the left (6.a) and right hand (6.c) expressions and the logical operator (6.b).

My code below in section 6 creates the following criteria

             WHERE RUNSTATUS = '9' AND OPRID = 'VP1'

/* 6. Create Query Criteria  
      In this example we'll setup     RUNSTATUS = 9 AND OPRID = VP1
**********************************************************************************/

/* 6.a. LEFT expression is a field */
&apiQueryCriteria = &apiQuerySelect.AddCriteria("criteria_1");
&apiQueryCriteria.logical = %Query_CombAnd;
&apiQueryCriteria.Expr1Type = %Query_ExprField;
&apiQueryCriteria.AddExpr1Field("A", "RUNSTATUS");

/* 6.b. Set expression operator */
&apiQueryCriteria.Operator = %Query_CondEqual;

/* 6.c. RIGHT expression is a constant */
&apiQueryCriteria.Expr2Type = %Query_ExprConstant;
&apiQueryCriteria.Expr2Constant1 = "9";

/* Second criteria row 
************************/

/* 6.a. LEFT expression is a field */
&apiQueryCriteria = &apiQuerySelect.AddCriteria("criteria_2");
&apiQueryCriteria.logical = %Query_CombAnd;
&apiQueryCriteria.Expr1Type = %Query_ExprField;
&apiQueryCriteria.AddExpr1Field("A", "OPRID");

/* 6.b. Set expression operator */
&apiQueryCriteria.Operator = %Query_CondEqual;

/* 6.c. RIGHT expression is a constant */
&apiQueryCriteria.Expr2Type = %Query_ExprConstant;
&apiQueryCriteria.Expr2Constant1 = "VP1";

7. Saving the Query will perform all sorts of validation and generate a useful collection of error messages.  Just remember "garbage in - garbage out".  The PIA Query Manager prevents the user from creating most illogical combinations when designing a Query so you're going to have to handle all possible error conditions in your code.

/* 7. Save the Query and handle any error messages 
*************************************************************************/
&res = &apiQuery.Save();
If &res <> 0 Then
   &colMessages = %Session.PSMessages;
   For &i = 1 To &colMessages.Count
      &errorObject = &colMessages.Item(&i);
      &messageText = &messageText | &errorObject.Text;
   End-For;
   Error ("An error has occurred saving the query: " | &messageText);
   
   &colMessages.DeleteAll();
End-If;

8. At this stage, assuming your Query has saved, you can open the Query in PIA Query Manager/Viewer and execute it.  But my example below includes the generation of a Query URL and a New Window execute by simply viewing the URL.

/* 8. Run PeopleSoft Query to new Window 
**************************************************************************/
&isPublic = False;
&isNewWindow = True;

Local string &url = GenerateQueryContentURL(%Portal, %Node, &qryName, &isPublic, &isNewWindow);

ViewContentURL(&url, &isNewWindow);

Always happy to field questions, comments, observations, improvements or corrections.  Hope you found this a useful starter to creating Query Definitions in PeopleCode .