Query Classes Examples

The following are examples of the usual actions that you perform using the query classes.

In this example, you are creating a new query, adding a record and two fields. The following is the complete code sample: the steps explain each line.

Local ApiObject &aQuery, &aQrySelCol;  
Local ApiObject &COLL, &ERROR; 
Local String &TEXT; 
Local Session &MySession; 
Local Record &aQryRcd; 
Local Field &aQryFld; 
 
&MySession = %Session; 
 
If &MySession <> Null Then 
 
   &aQuery = &MySession.GetQuery(); 
    
   &aQuery.Create("TEST1", False, %Query_Query, "PIA Test 1", "Creating Test Query 1 from PIA Page"); 
 
   &aQrySel = &aQuery.AddQuerySelect(); 
    
   &aQryRcd = &aQrySel.AddQueryRecord("ABSENCE_HIST"); 
   &aQryFld = &aQrySel.AddQuerySelectedField("ABSENCE_HIST", "A", "EMPLID", "ID"); 
    
   If &aQryFld <> Null Then 
         &aQryFld.ColumnNumber = 1; 
         &aQryFld.HeadingType = %Query_HdgRftShort; 
      End-If; 
 
   &Rslt = &aQuery.Save(); 
    
   If &Rslt <> 0 Then  
      /* save didn't complete */ 
      &COLL = &MySession.PSMessages; 
    
      For &I = 1 to &COLL.Count 
         &ERROR = &COLL.Item(&I); 
         &TEXT = &ERROR.Text; 
         /* do error processing */ 
      End-For; 
    
      &COLL.DeleteAll(); 
   End-if; 
    /* error processing for not getting a session */ 
End-if;

To create a new query:

  1. Get a session object.

    Before you can create a query, you have to get a session object. The session controls access to the query, provides error tracing, enables you to set the runtime environment, and so on. Then this program checks to verify that the session object is valid.

    &MySession = %Session;

&aQuery = &MySession.GetQuery();

If &MySession <> Null Then

  2. Create the query.

    Use the Create method to create the query. This query is a private query, of type query.

    &aQuery.Create("TEST1", False, %Query_Query, "Test 1", "Creating Test Query");

  3. Add a QuerySelect.

    The QuerySelect contains the main query statement for the query. There can be multiple QuerySelect objects for queries that involve unions or subqueries. Each select (or union or subquery) consists of QueryRecords, QueryOutputFields, QuerySelectedFields, and QueryCriteria and is treated as a child of the MAIN select statement.

    &aQrySel = &aQuery.AddQuerySelect();

  4. Add a record and a field.

    The AddQueryRecord method adds a query record to the query. The AddQuerySelectedField adds a field, using the record alias "A". The ID is what gets displayed in the heading for the query.

    &aQryRcd = &aQrySel.AddQueryRecord("ABSENCE_HIST"); 
&aQryFld = &aQrySel.AddQuerySelectedField("ABSENCE_HIST", "A", "EMPLID", "ID");

  5. Make the field an output field.

    The field was added as a selected field. By setting the ColumnNumber to a number greater than one, the field is now an output field. The text that's displayed in the heading comes from the RFT short description of the field.

          If &aQryFld <> Null Then 
         &aQryFld.ColumnNumber = 1; 
         &aQryFld.HeadingType = %Query_HdgRftShort; 
      End-If;

  6. Save the data.

    When you execute the Save method, the new query is saved to the database. 

    &Rslt = &aQuery.Save();
    If &Rslt <> 0 Then

    The Save method returns a numeric value: 0 if successful. You can use this value to do error checking.

  7. Check Errors.

    You can check if there were any errors using the PSMessages property on the session object. 

       /* save didn't complete */ 
   &COLL = &MySession.PSMessages; 
   For &I = 1 to &COLL.Count 
      &ERROR = &COLL.Item(&I); 
      &TEXT = &ERROR.Text; 
      /* do error processing */ 
   End-For; 
   &COLL.DeleteAll(); 
End-if;

    If there are multiple errors, all errors are logged to the PSMessages collection, not just the first occurrence of an error. As you correct each error, you may want to delete it from the PSMessages collection.

In this example, you are accessing an existing query, then adding criteria both as part of the query as well as part of a subquery. The SQL statement created by this subquery is as follows:

SELECT RECNAME, RECDESCR, RELLANGRECNAME, PARENTRECNAME, DESCRLONG from PSRECDEFN where RECNAME IN (select OBJECTVALUE1 from PSPROJECTITEM where PROJECTNAME = 'PPLTOOLS') AND RECTYPE = 0 order by RECNAME

The following is the complete code sample: the steps explain each line.

Local ApiObject &MyQuery, &MainQrySel, &Criteria1, &MyCrit2Expr2, &MyCriteria2;  
Local ApiObject &SubQrySel, &SubQryCrit1, &SubQryExpr1, &MyCrit2Expr2; 
Local Record &SubQryRec; 
Local Session = &MySession; 
Local ApiObject &COLL, &ERROR; 
Local String &TEXT; 
 
&MySession = %Session; 
 
If &MySession <> Null Then 
 
&MyQuery = &MySession.GetQuery(); 
 
&MyQuery.Open("Table", False, True); 
 
&MainQrySel = &MyQuery.QuerySelect; 
 
/* Add query record, add fields, then make selected fields output fields */ 
 
&MainRec = &MainQrySel.AddQueryRecord("PSRECDEFN"); 
 
&QryFld = &MainQrySel.AddQuerySelectedField(&MainRec.Name, &MainRec.RecordAlias, "RECNAME", "Record Name"); 
&QryFld.ColumnNumber = 1; 
&QryFld.OrderByNumber = 1; 
 
&QryFld = &MainQrySel.AddQuerySelectedField(&MainRec.Name, &MainRec.RecordAlias, "RECDESCR", "Record Descr"); 
&QryFld.ColumnNumber = 2; 
&QryFld.OrderByNumber = 2; 
 
&QryFld = &MainQrySel.AddQuerySelectedField(&MainRec.Name, &MainRec.RecordAlias, "RELLANGRECNAME", "Record Lang Rec"); 
&QryFld.ColumnNumber = 3; 
&QryFld.OrderByNumber = 3; 
 
&QryFld = &MainQrySel.AddQuerySelectedField(&MainRec.Name, &MainRec.RecordAlias, "PARENTRECNAME", "Parent Record Name"); 
&QryFld.ColumnNumber = 4; 
 
&QryFld = &MainQrySel.AddQuerySelectedField(&MainRec.Name, &MainRec.RecordAlias, "DESCRLONG", "Long Descr"); 
&QryFld.ColumnNumber = 5; 
 
&QryFld = &MainQrySel.AddQuerySelectedField(&MainRec.Name, &MainRec.RecordAlias, "EMPLID", "ID"); 
&QryFld.ColumnNumber = 6; 
 
/* adding first criteria */ 
&Criteria1 = &MainQrySel.AddCriteria("FirstCriteria"); 
 
/* First criteria will not have any logical AND/OR */ 
&Criteria1.Logical = %Query_CombNotUsed;  
 
&Criteria1.Expr1Type = %Query_ExprField; 
&Criteria1.AddExpr1Field(&MainRec.RecordAlias, "RECNAME"); 
 
/* So that the criteria is constructed as - RECNAME IN (...)*/ 
&Criteria1.Operator = %Query_CondInList;  
&Criteria1.Expr2Type = %Query_ExprSubQuery; 
 
&SubQrySel = &Criteria1.AddExpr2SubQuery(); 
&SubQryRec = AddQueryRecord("PSPROJECTITEM"); 
&SubQryFld1 = &SubQrySel.AddQuerySelectedField(&SubQryRec.Name, &SubQryRec.RecordAlias, "OBJECTVALUE1", "Join Object") 
&SubQryFld1.ColumnNumber = 1; 
 
/* Need criteria - PROJECTNAME = 'PPLTOOLS'  - in the subquery */ 
&SubQryCrit1 = &SubQrySel.AddCriteria("FirstSubCrit"); 
 
/* First criteria will not have any logical AND/OR */ 
&SubQryCrit1.Logical = %Query_CombNotUsed; 
 
&SubQryCrit1.Expr1Type = %Query_ExprField; 
&SubQryCrit1.AddExpr1Field(&SubQryRec.RecordAlias, "PROJECTNAME"); 
&SubQryCrit1.Operator = %Query_CondEqual;  
 
/* So that the criteria is constructed as - PROJECTNAME = 'PPLTOOLS'*/ 
&SubQryCrit1.Expr2Type = %Query_ExprConstant; 
&SubQryExpr1 = &SubQryCrit1.AddExpr2Expression(); 
&SubQryExpr1.Text = "PPLTOOLS"; 
&SubQryCrit1.Expr2Expression1 = &SubQryExpr1; 
 
/* Second Criteria, which is for RECTYPE = 0 */ 
&MyCriteria2 = &MainQrySel.AddCriteria("SecondCriteria"); 
 
&MyCriteria2.Expr1Type = %Query_ExprField;  
&MyCriteria2.AddExpr1Field(&MainRec.RecordAlias, "RECTYPE");  
 
/* Since this is second criteria, we need a logical AND to state that */
/* - AND RECTYPE = 0 */ 
 
&MyCriteria2.Logical = %Query_CombAnd; 
 
&MyCriteria2.Operator = %Query_CondEqual;  
&MyCriteria2.Expr2Type = %Query_ExprConstant;  
&MyCrit2Expr2 = &MyCriteria2.AddExpr2Expression(); 
&MyCriteria2.Expr2Expression1 = &MyCrit2Expr2; 
&MyCrit2Expr2.Text = "0"; 
 
&Rslt = &MyQuery.Save(); 
 
If &Rslt <> 0 Then  
   /* save didn't complete */ 
   &COLL = &MySession.PSMessages; 
 
      For &I = 1 to &COLL.Count 
         &ERROR = &COLL.Item(&I); 
         &TEXT = &ERROR.Text; 
         /* do error processing */ 
      End-For; 
       
      &COLL.DeleteAll(); 
   End-if; 
    
Else 
 
/* do error processing for not getting session */ 
 
End-if;

To add criteria to a query:

  1. Get a session object.

    Before you can create a query, you have to get a session object. The session controls access to the query, provides error tracing, enables you to set the runtime environment, and so on. Then this program checks to verify that the session object is valid.

    &MySession = %Session;

&MyQuery = &MySession.GetQuery();

If &MySession <> Null Then

  2. Access an existing query and get the main query select statement.

    Use the Open method to get the existing query. Then access the main select statement with the QuerySelect property. 

    &MyQuery.Open("Table", False, True); 
 
&MainQrySel = &MyQuery.QuerySelect;

  3. Add Query Record in the Main Select.

    Add the query record that you want to use. 

    &MainRec = &MainQrySel.AddQueryRecord("PSRECDEFN");

  4. Add the displayed fields.

    You want to add the selected fields. Note instead of hardcoding the name of the record, this code example uses the Name property. Also, the code uses the RecordAlias property instead of hardcoding the alias. This makes the code easier to read, as well as easier to maintain. Specifying a column number also makes this an output field. 

    &QryFld = &MainQrySel.AddQuerySelectedField(&MainRec.Name, &MainRec.RecordAlias, "RECNAME", "Record Name"); 
&QryFld.ColumnNumber = 1;

  5. Specify the OrderBy value.

    Because we need to order by this field, the OrderByNumber of that field must be set also. 

    &QryFld.OrderByNumber = 1;

  6. Add the first criteria.

    Add the first criteria. You don't want it added with any kind of operator, like an AND or an OR, so the Logical property of the first criteria is set with %Query_CombNotUsed. This is also used because it's the first non-having criteria of a query.

    /* adding first criteria */ 
&Criteria1 = &MainQrySel.AddCriteria("FirstCriteria"); 
 
/* First criteria will not have any logical AND/OR */ 
   &Criteria1.Logical = %Query_CombNotUsed;

  7. Add the first criteria field.

    The first field for the criteria is a QueryExpression type field. The type of the field must be set before the field is added. 

       &Criteria1.Expr1Type = %Query_ExprField; 
   &Criteria1.AddExpr1Field(&MainRec.RecordAlias, "RECNAME");

  8. Add the condition for the first criteria and the subquery.

    The first criteria is the WHERE RECNAME IN portion of the SQL statement. The condition is considered 'in list', where the list is the result of the subquery. The expression is a subquery. Again, you have to set the type again before adding the subquery. 

    &Criteria1.Operator = %Query_CondInList;  
&Criteria1.Expr2Type = %Query_ExprSubQuery; 
&SubQrySel = &Criteria1.AddExpr2SubQuery();

  9. Add the records for the subquery.

    Add the query record and the field from the query field, and make it an output field.

    &SubQryRec = AddQueryRecord("PSPROJECTITEM"); 
&SubQryFld1 = &SubQrySel.AddQuerySelectedField(&SubQryRecName.Name, &SubQryRec.RecordAlias, "OBJECTVALUE1", "Join Object"); 
&SubQryFld1.ColumnNumber = 1;

  10. Add the criteria in the subquery.

    The following code adds the criteria for the subquery. Because this is the first non-having criteria in a select statement, the Logical property is set as %Query_CombNotUsed. Then the first expression is added as a field, and set to be equal to the second expression, PPLTOOLS. This is the where PROJECTNAME = 'PPLTOOLS' portion of the SQL statement.

    /* Need criteria - PROJECTNAME = 'PPLTOOLS'  - in the subquery */ 
&SubQryCrit1 = &SubQrySel.AddCriteria("FirstSubQryCrit"); 
 
/* First criteria will not have any logical AND/OR */ 
   &SubQryCrit1.Logical = %Query_CombNotUsed; 
 
   &SubQryCrit1.Expr1Type = %Query_ExprField; 
   &SubQryCrit1.AddExpr1Field(&SubQryRec.RecordAlias, "PROJECTNAME"); 
&SubQryCrit1.Operator = %Query_CondEqual;  
 
/* So that the criteria is constructed as - PROJECTNAME = 'PPLTOOLS'*/ 
&SubQryCrit1.Expr2Type = %Query_ExprConstant; 
&SubQryExpr1 = &SubQryCrit1.AddExpr2Expression(); 
&SubQryExpr1.Text = "PPLTOOLS"; 
   &SubQryCrit1.Expr2Expression1 = &SubQryExpr1;

  11. Add the second criteria to the main select.

    Add the second criteria. Remember to set the type for the expression field first. Because this is the second criteria, we need a logical AND to state that this criteria is used with the first criteria. 

    /* Second Criteria, which is for RECTYPE = 0 */ 
&MyCriteria2 = &MainQrySel.AddCriteria("SecondCriteria"); 
 
&MyCriteria2.Expr1Type = %Query_ExprField;  
&MyCriteria2.AddExpr1Field(&MainRec.RecordAlias, "RECTYPE");  
 
/* Since this is second criteria, we need a logical AND to state that*/ 
/* - AND RECTYPE = 0 */ 
 
&MyCriteria2.Logical = %Query_CombAnd; 
 
&MyCriteria2.Operator = %Query_CondEqual;  
&MyCriteria2.Expr2Type = %Query_ExprConstant;  
 
&MyCrit2Expr2 = &MyCriteria2.AddExpr2Expression(); 
&MyCriteria2.Expr2Expression1 = &MyCrit2Expr2; 
&MyCrit2Expr2.Text = "0";

  12. Save the data.

    When you execute the Save method, the new query is saved to the database. 

    &Rslt = &MyQuery.Save();
    If &Rslt <> 0 Then

    The Save method returns a numeric value: 0 if successful. You can use this value to do error checking.

  13. Check Errors

    You can check if there were any errors using the PSMessages property on the session object. 

       /* save didn't complete */ 
   &COLL = &MySession.PSMessages; 
   For &I = 1 to &COLL.Count 
      &ERROR = &COLL.Item(&I); 
      &TEXT = &ERROR.Text; 
      /* do error processing */ 
   End-For; 
   &COLL.DeleteAll(); 
End-if;

    If there are multiple errors, all errors are logged to the PSMessages collection, not just the first occurrence of an error. As you correct each error, you may want to delete it from the PSMessages collection.

The following PeopleCode query uses outer joins:

   Local ApiObject &aQuery, &aQrySelCol;
   Local ApiObject &aQryRcd, &aQryRcd2;
   Local ApiObject &aQryFld, &aQryFld2;
   Local ApiObject &aQrySel, &Criteria1;
   Local number &Rslt;
   
   &aQuery = %Session.GetQuery();
   &aQuery.Create("TEST1", False, %Query_Query, "PIA Test 1", "Creating Test Query1 from PIA Page");
   
   &aQrySel = &aQuery.AddQuerySelect();
   
   &aQryRcd = &aQrySel.AddQueryRecord("JOB");
   &aQryRcd.RecordAlias = "A";
   
   &aQryRcd2 = &aQrySel.AddQueryRecord("PERSONAL_DATA");
   &aQryRcd2.RecordAlias = "B";
   &aQryRcd2.JoinType = %Query_JoinLeftOuter;
   &aQryRcd2.JoinAlias = "A";
   
   &aQryFld = &aQrySel.AddQuerySelectedField("JOB", "A", "EMPLID", "EMPLID");
   &aQryFld.ColumnNumber = 1;
   &aQryFld.HeadingType = %Query_HdgRftShort;
   
   &aQryFld2 = &aQrySel.AddQuerySelectedField("PERSONAL_DATA", "B", "NAME", "NAME");
   &aQryFld2.ColumnNumber = 2;
   &aQryFld.HeadingType = %Query_HdgRftShort;
   
   &Criteria1 = &aQrySel.AddCriteria("JoinCriteria");
   &Criteria1.Logical = %Query_CombNotUsed;
   &Criteria1.Expr1Type = %Query_ExprField;
   &Criteria1.AddExpr1Field("A", "EMPLID");
   &Criteria1.Operator = %Query_CondEqual;
   &Criteria1.Expr2Type = %Query_ExprField;
   &Criteria1.AddExpr2Field1("B", "EMPLID");
   &Criteria1.OJAlias = "B";
   
   &Rslt = &aQuery.Save();

The above PeopleCode program produces the following SQL:

SELECT A.EMPLID, B.NAME 
FROM (PS_JOB A LEFT OUTER JOIN PS_PERSONAL_DATA B ON A.EMPLID=B.EMPLID)