Using the Select and SelectNew Methods

This section provides an overview of the Select method and discusses how to use the Select method.

The Select and SelectNew methods, like the ScrollSelect functions, enable you to control the process of selecting data into a page scroll area. The Select method selects rows from a table or view and adds the rows to either a rowset or a row. Let’s call the record definition of the table or view that it selected from the select record. Let’s call the primary database record of the top-level rowset object executing the method the default scroll record.

The select record can be the same as the default scroll record, or it can be a different record definition that has the same key fields as the default scroll record. If you define a select record that differs from the default scroll record, you can restrict the number of fields loaded into the buffers by including only the fields you actually need.

You can use these methods only with a rowset. A rowset can be thought of as a page scroll area.

A level zero rowset starts at the top level of the page, level zero, and contains all the data in the component buffers. A child rowset is contained by an upper-level rowset, also called the parent rowset. For example, a level one rowset could be considered the child rowset of a level zero, or parent, rowset. Or a level two rowset could be the child rowset of a level one rowset. The data contained in a child rowset depends on the row of the parent rowset.

When a rowset is selected into, any autoselected child rowsets are also read. The child rowsets are read using a Where clause that filters the rows according to the Where clause used for the parent rowset, using a Subselect.

The Select method automatically places child rowsets in the rowset object executing the method under the correct parent row. If it cannot match a child rowset to a parent row, an error occurs.

The Select method also accepts an optional SQL string that can contain a Where clause restricting the number of rows selected into the scroll area. The SQL string can also contain an Order By clause, enabling you to sort the rows.

The Select and SelectNew methods generate an SQL Select statement at runtime, based on the fields in the select record and the Where clause passed to them in the function call. This gives Select and SelectNew a significant advantage over the SQLExec function: they enable you to change the structure of the select record without affecting the PeopleCode program, unless the field affected is referred to in the Where clause string. This can make the application easier to maintain.

Also, if you use one of the meta-SQL constructs or shortcuts in the Where clause, such as %KeyEqual or %List, even if a field has changed, you do not have to change your code.

Unlike the ScrollSelect functions, neither Select or SelectNew allow you to operate in turbo mode.

Note: In addition to these methods, the SelectByKey record class method enables you to select into a record object. If you’re only interested in selecting a single row of data, consider this method instead.

The syntax of the Select method is: 

Select([parmlist], RECORD.selrecord [, wherestr, bindvars]);

Where paramlist is a list of child rowsets, given in the following form:

SCROLL.scrollname1 [SCROLL., scrollname2] . . .

The first scrollname must be a child rowset of the rowset object executing the method, the second scrollname must be a child of the first child, and so on.

This syntax does the following:

  • Specifies an optional child rowset into which to read the selected rows.

  • Specifies the select record from which to select rows.

  • Passes a string containing a SQL Where clause to restrict the selection of rows or an Order By clause to sort the rows, or both.

Specifying Child Rowsets

The first part of the Select syntax specifies a child rowset into which rows are selected. This parameter is optional.

If you do not specify any child rowsets in paramlist, Select selects from a SQL table or view specified by selrecord into the rowset object executing the method. For example, suppose you’ve instantiated a level one rowset &BUS_EXPENSES_PER. The following would select into this rowset:

Local Rowset &BUS_EXPENSES_PER; 
 
&BUS_EXPENSES_PER = GetRowset(SCROLL.BUS_EXPSNESE_PER);
&BUS_EXPENSES_PER.Select(RECORD.BUS_EXPENSE_VW, 
"WHERE SETID = :1 and CUST_ID = :2", SETID, CUST_ID);

If the rowset executing the method is a level zero rowset, and you specify the Select method without specifying any child rowsets with paramlist,, the method reads only a single row, because only one row is allowed at level zero.

Note: For developers familiar with previous releases of PeopleCode: In this situation, the Select method is acting like the RowScrollSelect function.

If you specify a child rowset in paramlist, the Select method selects from a SQL table or view specified by selrecord into the child rowset specified in paramlist, under the appropriate row of the rowset executing the method.

In the following example, rows are selected into a child rowset BUS_EXPENSE_DTL, matching level-one keys, and with the charge amount equal to or exceeding 200, sorting by that amount:

Local Record &REC_EXP; 
Local Rowset &BUS_EXPENSE_PER; 
 
&REC_EXP = GetRecord(RECORD.BUSINESS_EXPENSE_PER;
&BUS_EXPENSE_PER = GetRowset(SCROLL.BUS_EXPSNESE_PER);
&BUS_EXPENSE_PER.Select(SCROLL.BUS_EXPENSE_DTL, 
RECORD.BUS_EXPENSE_DTL, "WHERE %KeyEqual(:1) AND EXPENSE_AMT 
>= 200 ORDER BY EXPENSE_AMT", &REC_EXP);

Specifying the Select Record

The record definition of the table or view being selected from is called the select record, and identified with RECORD.selrecord.. The select record can be the same as the primary database record associated with the rowset executing the method, or it can be a different record definition that has compatible fields.

The select record must be defined in PeopleSoft Application Designer and be a built SQL table or view (using Build, Project), unless the select record is the same record as the primary database record associated with the rowset.

The select record can contain fewer fields than the primary record associated with the rowset, although it must contain any key fields to maintain dependencies with other records.

If you define a select record that differs from the primary database record for the rowset, you can restrict the number of fields that are loaded into the buffers on the client work station by including only the fields you actually need.

The Where Clause

The Select method accepts a SQL string that can contain a Where clause restricting the number of rows selected into the object. The SQL string can also contain an Order By clause to sort the rows.

Select and SelectNew generate a SQL Select statement at runtime, based on the fields in the select record and the Where clause passed to them in the method parameters.

To avoid errors, the Where clause should explicitly select matching key fields on parent and child rows. You do this using the %KeyEqual meta-SQL.

Select Like RowScrollSelect

If the rowset executing the method is a level zero rowset, and you specify Select without specifying any child rowsets with paramlist, the method reads only a single row, because only one row is allowed at level zero.

Note: For developers familiar with previous releases of PeopleCode: In this situation, the Select method is acting like the RowScrollSelect function.

If you qualify the lower-level rowset so that it only returns one row, it acts like the RowScrollSelect method. 

&RSLVL1 = GetRowset(SCROLL.PHYSICAL_INV); 
&RSLVL2 = &RSLVL1(&PHYSICAL_ROW).GetRowset(SCROLL.PO_RECEIVED_INV); 
&REC2 = &RSLVL2.PO_RECEIVED_INV; 
If &PO_ROW = 0 Then 
   &RSLVL2.Select(PO_RECEIVED_INV, "WHERE %KeyEqual(:1) 
and qty_available > 0", &REC2); 
End-if;