Understanding the Select and SelectNew Methods

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.