%Select meta-SQL element

Syntax

%Select(statefield1[,statefield2]...[,statefieldN])
Select field1[,field2]...[,fieldN]

The statefields must be valid fields on the state record (they may be fieldname or recordname.fieldname, as with %Bind), and fields must be either valid fields in the From tables or hard-coded values.

Description

Use the %Select construct to identify the state record fields to hold the values returned by the corresponding Select statement. The %Select construct is required at the beginning of all Select statements. For example, you need one in the flow control actions and one in the SQL actions that contain a Select statement.

You use the %Select construct to pass variables to the state record, and you use the %Bind construct to retrieve or reference the variables.

Example

Consider the following sample statement:

%SELECT(BUSINESS_UNIT,CUST_ID)
SELECT BUSINESS_UNIT, CUST_ID
FROM PS_CUST_DATA
   WHERE PROCESS_INSTANCE = %BIND(PROCESS_INSTANCE)

The following steps illustrate the execution of the previous statement:

  1. Resolve bind variables.

    The string %Bind(PROCESS_INSTANCE) is replaced with the value of the state record field called PROCESS_INSTANCE.

  2. Execute the SQL Select statement.

  3. Perform a SQL Fetch statement.

    If a row is returned, the state record fields BUSINESS_UNIT and CUST_ID are updated with the results. If the Fetch statement does not return any rows, all fields in the %Select construct retain their previous values.

Note:

All fields referenced by a %Select construct must be defined in the associated state record. Also, aggregate functions always return a row, so they always cause the state record to be updated. As such, for aggregate functions, no difference exists between using %SelectInit or %Select.