Select method: Rowset class

Syntax

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.

Description

Select, like the related method SelectNew, reads data from the database tables or views into either a row or rowset object.

Note:

This method is valid only when used with rowsets that reference data from the component buffers. You cannot use this method with a message rowset, a rowset from an Application Engine state record, and so on. You can’t use this method with rowsets created using the CreateRowset function (use the Fill method instead.)

Select 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.

When a rowset is selected into, any autoselected child rowsets is 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.

If you don’t specify any child rowsets in paramlist, Select selects from a SQL table or view specified by selrecord into the rowset object executing the method.

If you specify a child rowset in paramlist, Select 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.

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.

The rowset executing the method does not have to be a level zero rowset, so the Select method can produce the functionality of both the ScrollSelect and RowScrollSelect functions.

Note:

For developers familiar with previous releases of PeopleCode: If the rowset executing the method is a level zero rowset, and you specify a child rowset with paramlist, this method functions exactly like ScrollSelect. If the rowset executing the method is not a level zero rowset, and no child rowsets are specified with paramlist, the method acts like RowScrollSelect.

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, or it can be a different record definition that has compatible fields. 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 workstation by including only the fields that you actually need.

Select 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 its related methods 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.

For rowsets corresponding to component buffer data, the Select method executes in turbo mode only—that is, default processing is performed, but only on the row being selected. Additional information on turbo mode and non-turbo mode is available in the documentation with the InsertRow PeopleCode built-in function.

See PeopleCode Language Reference: InsertRow function.

Parameters

Parameter	Description
paramlist	An optional parameter list, for specifying children rowsets of the rowset executing the method, as the rowset to be selected into. The first scrollname in paramlist must be a child rowset of the rowset executing the method, the second scrollname must be a child of the first child, and so on.
RECORD.selrecord	Specifies the select record. The selrecord record must be defined in Application Designer and be a build SQL table (using Build, Project) as a table or a view, unless selrecord is the same record as the primary database record associated with the rowset. selrecord 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.
wherestr	Contains a WHERE clause to restrict the rows selected from selrecord and/or an ORDER BY clause to sort the rows. The WHERE clause can contain the PeopleSoft meta-SQL functions such as %DateIn() or %CurrentDateIn. It can also contain inline bind variables.
bindvars	A list of bind variables to be substituted in the WHERE clause. The same restrictions that exist for SQLExec exist for these variables. See SQLExec for further information.

Returns

The number of rows read (optional.) This counts only the lines read into the specified rowset. It does not include any additional rows read into any child rowsets of the rowset.

Example

The following example selects into the level zero rowset, only one row. Depending on the autoselect settings on any child scrolls, they may be reselected too.

&LEVEL0 = GetLevel0();
&LEVEL0.Select(RECORD.PERSONAL_DATA, "WHERE.  .  .");

The following example selects into the level one scroll specified. Depending on the autoselect settings on any child (level two) scrolls, they may also be selected.

&LEVEL0.Select(SCROLL.EMPL_CHECKLIST, RECORD.EMPL_CHECKLIST, "WHERE.  .  .");

The following example selects into the child scroll of the level one rowset. Each row fetched is placed under the appropriate row in &LEVEL1. Note that instead of hard-coding the WHERE clause, the SQL repository is used to access a SQL definition named SELECT_WHERE.

&LEVEL1 = &LEVEL0()(1).GetRowset(SCROLL.EMPL_CHECKLIST);
&LEVEL1.Select(SCROLL.EMPL_CHKLST_ITM, RECORD.EMPL_CHKLST_ITM, SQL.SELECT_WHERE);

The following example first flushes the hidden work scroll, then selects into it based on a field on the page.

&RS1H.Flush();
&RS1H.Select(RECORD.CHECKLIST_ITEM, "where Checklist_CD = :1 and EffDt = (Select⇒
 Max(EffDt) from PS_CHECKLIST_ITEM Where CheckList_CD = :2)", CHECKLIST_CD,⇒
 CHECKLIST_CD);