SSELECT statement

Syntax

SSELECT [variable] [TO list.number] [ON ERROR statements]
SSELECTN [variable] [TO list.number] [ON ERROR statements]
SSELECTV [variable] TO list.variable [ON ERROR statements]

Description

Use an SSELECT statement to create a numbered select list of record IDs in sorted order from an InfoSphere® DataStage® file or a dynamic array. You can then access this select list by a subsequent READNEXT statement which removes one record ID at a time from the list.

variable can specify a dynamic array or a file variable. If it specifies a dynamic array, the record IDs must be separated by field marks (ASCII 254). If variable specifies a file variable, the file variable must have previously been opened. If variable is not specified, the default file is assumed (for more information on default files, see the OPEN statement). If the file is neither accessible nor open, or if variable evaluates to the null value, the SSELECT statement fails and the program terminates with a run-time error message.

If the file is an SQL table, the effective user of the program must have SQL SELECT privilege to read records in the file. For information about the effective user of a program, see the AUTHORIZATION statement.

You must use a file lock with the SSELECT statement when it is within a transaction running at isolation level 4 (serializable). This prevents phantom reads.

The TO clause specifies the select list that is to be used. list.number is an integer from 0 through 10. If no list.number is specified, select list 0 is used.

The record IDs of all the records in the file form the list. The record IDs are listed in ascending order. Each record ID is one entry in the list.

You often want a select list with the record IDs in an order different from their stored order or with a subset of the record IDs selected by some specific criteria. To do this, use the SELECT statements or SSELECT commands in a BASIC EXECUTE statement. Processing the list by READNEXT statement is the same, regardless of how the list is created.

Use the SSELECTV statement to store the select list in a named list variable instead of to a numbered select list. list.variable is an expression that evaluates to a valid variable name. This is the default behavior of the SSELECT statement in PICK, REALITY, and IN2 flavor accounts. You can also use the VAR.SELECT option of the $OPTIONS statement to make the SSELECT statement act as it does in PICK, REALITY, and IN2 flavor accounts.

In NLS mode when locales are enabled, the SSELECT statements use the Collate convention of the current locale to determine the collating order.

The ON ERROR Clause

The ON ERROR clause is optional in SSELECT statements. The ON ERROR clause lets you specify an alternative for program termination when a fatal error is encountered during processing of a SSELECT statement.

If a fatal error occurs, and the ON ERROR clause was not specified, or was ignored (as in the case of an active transaction), the following occurs:

  • An error message appears.
  • Any uncommitted transactions begun within the current execution environment roll back.
  • The current program terminates.
  • Processing continues with the next statement of the previous execution environment, or the program returns to the command prompt.

A fatal error can occur if any of the following occur:

  • A file is not open.
  • file.variable is the null value.
  • A distributed file contains a part file that cannot be accessed.

If the ON ERROR clause is used, the value returned by the STATUS function is the error number.

PICK, REALITY, and IN2 Flavors

In a PICK, REALITY, or IN2 flavor account, the SSELECT statement has the following syntax:

SSELECT[V] [variable] TO list.variable
SSELECTN [variable] TO list.number

You can use either the SSELECT or the SSELECTV statement to create a select list and store it in a named list variable. The only useful thing you can do with a list variable is use a READNEXT statement to read the next element of the select list.

Use the SSELECTN statement to store the select list in a numbered select list. list.number is an expression that evaluates to a number from 0 through 10. You can also use the -VAR.SELECT option of the $OPTIONS statement to make the SSELECT statement act as it does in IDEAL and INFORMATION flavor accounts.

Example

The following example opens the file SUN.MEMBER to the file variable MEMBER.F, then creates an active sorted select list of record IDs. The READNEXT statement assigns the first record ID in the select list to the variable @ID, then prints it. Next, the file SUN.SPORT is opened to the file variable SPORT.F, and a sorted select list of its record IDs is stored as select list 1. The READNEXT statement assigns the first record ID in the select list to the variable A, then prints DONE.

OPEN '','SUN.MEMBER' ELSE PRINT "NOT OPEN"
SSELECT
READNEXT @ID THEN PRINT @ID
*
OPEN '','SUN.SPORT' ELSE PRINT "NOT OPEN"
SSELECT TO 1
READNEXT A FROM 1 THEN PRINT "DONE" ELSE PRINT "NOT"

This is the program output:

0001
DONE
Parent topic: BASIC Statements and Functions