Using the Select Method

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;