7 83 1 SELECT Parameters

7.83.1 SELECT Parameters

END_FILE

FIELDS

FROM_FILE

GENERIC

IO_ERROR

IO_STATUS

ISSUE_MSG

LOCK

NBR_KEYS

OPTIONS

RETURN_RRN

VAL_ERROR

WHERE

WITH_KEY

FIELDS

Specifies either the field(s) that are to be selected from the record in the file or the name of a group that specifies the field(s) to be selected. Alternatively, an expandable group expression can be entered in this parameter.

The following special values can be used:

• *ALL specifies that all fields from the currently active file be selected.

• *ALL_REAL specifies that all real fields from the currently active file be selected.

• *ALL_VIRT specifies that all virtual fields from the currently active file be selected.

• *EXCLUDING specifies that fields following this special value must be excluded from the field list.

• *INCLUDING specifies that fields following this special value must be included in the field list. This special value is only required after an *EXCLUDING entry has caused the field list to be in exclusion mode.

Note: When all fields are selected from a logical file maintained by OTHER, all the fields from the based-on physical file are included in the field list.

It is strongly recommended that the special values *ALL, *ALL_REAL or *ALL_VIRT in parameter FIELDS be used sparingly and only when strictly required. Selecting fields which are not needed invalidates cross-reference details (shows fields which are not used in the function) and increases the Crude Entity Complexity Rating of the function pointlessly.

Note that when BLOB or CLOB data is retrieved, it is either *SQLNULL or a filename. If a filename, the data from the database file has been copied into the file.

Warning: It is time-consuming to retrieve BLOB or CLOB fields from a file.

Recommended Database Design When Using BLOB and CLOB Fields

The recommended design when using BLOB and CLOB fields is to put them in a separate file from the rest of the fields using the same key as the main file. This forces programmers to do separate IOs to access the BLOB and CLOB data, thus reducing impact on database performance from indiscriminate use of this data. It is also the most portable design ensuring that the non-BLOB and non-CLOB data can be quickly accessed at all times.

FROM_FILE

Refer to Specifying File Names in I/O commands .

WHERE

Refer to Specifying Conditions and Expressions and Specifying WHERE Parameter in I/O Commands.

After a SELECT/ENDSELECT loop utilizing a where condition, the contents of the fields are unpredictable. The records matching the where condition should only be processed within the SELECT/ENDSELECT loop.

WITH_KEY

Refer to Specifying File Key Lists in I/O Commands .

NBR_KEYS

This parameter can be used in conjunction with the WITH_KEY parameter to vary the number of key fields that are actually used to retrieve records at execution time.

*WITHKEY, which is the default value, specifies that the number of key fields will always match the number specified in the WITH_KEY parameter and the value will not be changed at execution time.

*COMPUTE can also be specified for this parameter. This specifies that the number of keys should be determined by examining the contents of the fields nominated in the WITH_KEY parameter at execution time.

The logic used to determine the number of keys works like this:

Set <n> to number of fields specified in WITH_KEY parameter. 
Dowhile n is greater than zero and keyfield(n) is *NULL or *SQLNULL. 
Subtract 1 from n. 
Endwhile. 
Set <number of keys> to <n>.
 

For a definition of the *NULL value for each of the field types, refer to 7.9.1 CHANGE Parameters.

If you want to vary the number of key fields by direct RDML logic specify the name of a numeric field for this parameter. The field you name should contain the number of keys value at execution time. The field specified must be defined in this function or in the LANSA data dictionary and must be numeric.

At execution time the value contained in the NBR_KEYS field must be not less than zero and not greater than the number of key fields specified in the WITH_KEY parameter.

When a SELECT command is executed with the NBR_KEYS field set to zero the entire WITH_KEY parameter is effectively ignored for selection purposes.

Refer to the examples following for more information.

GENERIC

Specifies whether or not generic searching is required. Generic searching is different to full or partial key searching because only the non-blank or non-zero portion of the key value is used when comparing the search key with the file key.

When using generic searching on an alphanumeric field only the leftmost non-blank portion of the search field is compared with the file key (i.e., trailing blanks are ignored for comparative purposes).

When using generic searching on a numeric field only the leftmost non-zero portion of the search field is compared with the file key (ie: trailing zeros are ignored for comparative purposes). Also, generic numeric field comparisons are done as if both the search key and the file key are positive numbers, regardless of what they actually are.

Note that these generic search rules mean that a blank alphanumeric search key or a zero (0) numeric key will match every record selected.

For example, if a file was keyed by a name field and it contained the following values:

SM

SMIT

SMITH

SMITHS

SMITHY

SMYTHE

then the SELECT statement:

SELECT  WITH_KEY('SM')
 

would only select the first record in the file because it is the only record that matches the full key value 'SM'. If however, the SELECT statement was changed to:

SELECT  WITH_KEY('SM') GENERIC(*YES)
 

then all the records in the file would be selected because only the non-blank portion of the key value specified is compared with the file key.

*NO, which is the default value, indicates that generic searching is not required.

*YES indicates that generic searching is to be performed. When generic searching is used it is only actually performed on the last key that was supplied at execution time. Other (previous) keys specified in the WITH_KEY parameter must exactly match the values in the file. They are not generically compared with the data in the file.

For instance, imagine a name and address file that is keyed by state, post/zip code and name. The command:

SELECT  WITH_KEY('NSW' 2000 'SM') NBR_KEYS(3) GENERIC(*YES)
 

would select all names in NSW, with post code 2000 whose names start with SM. This is an example of generic searching on an alphanumeric field. Trailing blanks are ignored when comparing the search key with the data read from the file. Also note that only the last key ('SM') is actually generically compared with the data on the file. The other keys , 'NSW' and 2000 must exactly match data read from the file. If, at execution time the command was dynamically modified to use 2 keys, like this:

SELECT  WITH_KEY('NSW' 2000 'SM') NBR_KEYS(2) GENERIC(*YES)
 

then it would select all names in NSW with a post code that starts with 2 (ie: 2000 to 2999). This is an example of generic searching on a numeric field where trailing zeroes (0's) are ignored.

IO_STATUS

Specifies the name of a field that is to receive the "return code" that results from the I/O operation.

If the default value of *STATUS is used the return code is placed into a special field called #IO$STS which can be referenced in the RDML program just like any other field.

If a user field is nominated to receive the return code it must be alphanumeric with a length of 2. Even if a user field is nominated the special field #IO$STS is still updated.

For values, refer to I/O Return Codes .

IO_ERROR

Specifies what action is to be taken if an I/O error occurs when the command is executed.

An I/O error is considered to be a "fatal" error. Some examples are file not found, file is damaged, file cannot be allocated. These types of errors stop the function from performing any processing at all with the file involved.

If the default value of *ABORT is used the function will abort with error message(s) that indicate the nature of the I/O error.

*NEXT indicates that control should be passed to the next command. The purpose of *NEXT is to permit you to handle error messages in the RDML, and then ABORT, rather than use the default ABORT. (It is possible for processing to continue for LANSA for i and Visual LANSA, but this is NOT a recommended way to use LANSA.)
ER returned from a database operation is a fatal error and LANSA does not expect processing to continue. The IO Module is reset and further IO will be as if no previous IO on that file had occurred. Thus you must not make any presumptions as to the state of the file. For example, the last record read will not be set. A special case of an IO_ERROR is when a trigger function is coded to return ER in TRIG_RETC. The above description applies to this case as well.
Therefore, LANSA recommends that you do NOT use a return code of ER from a trigger function to cause anything but an ABORT or EXIT to occur before any further IO is performed.

*RETURN specifies that in a program mainline control is to be returned to the caller and in a subroutine control is to be returned to the caller routine or the program mainline.

If none of the previous values are used you must nominate a valid command label to which control should be passed.

VAL_ERROR

Specifies the action to be taken if a validation error was detected by the command.

A validation error occurs when information that is to be added, updated or deleted from the file does not pass the FILE or DICTIONARY level validation checks associated with fields in the file.

If the default value *LASTDIS is used control will be passed back to the last display screen used. The field(s) that failed the associated validation checks will be displayed in reverse image and the cursor positioned to the first field in error on the screen.

*NEXT indicates that control should be passed to the next command.

*RETURN specifies that in a program mainline control is to be returned to the caller and in a subroutine control is to be returned to the caller routine or the program mainline.

If none of the previous values are used you must nominate a valid command label to which control should be passed.

The *LASTDIS is valid even if there is no "last display" (such as in batch functions). In this case the function will abort with the appropriate error message(s).

When using *LASTDIS the "Last Display" must be at the same level as the database command (INSERT, UPDATE, DELETE, FETCH and SELECT).  If they are at different levels e.g. the database command is specified in a SUBROUTINE, but the "Last Display" is a caller routine or the mainline, the function will abort with the appropriate error message(s).

The same does NOT apply to the use of event routines and method routines in Visual LANSA. In these cases, control will be returned to the calling routine. The fields will display in error with messages returned to the first status bar encountered in the parent chain of forms, or if none exist, the first form with a status bar encountered in the execution stack (for example, a reusable part that inherits from PRIM_OBJT).

END_FILE

Specifies what is to happen when the "end of the file" is reached. Note that the "end of the file" means the last record that matches the selection criteria has been processed, not necessarily the last record in the file has been processed.

*NEXT indicates that control should be passed to the next command.

*RETURN specifies that in a program mainline control is to be returned to the caller and in a subroutine control is to be returned to the caller routine or the program mainline.

If none of the previous values are used you must nominate a valid command label to which control should be passed.

Values may not be as expected on exit from a select loop due to an extra record being read to determine when to terminate the select loop.

ISSUE_MSG

Specifies whether an "end of file" message is to be automatically issued or not.

The default value is *NO which indicates that no message should be issued.

The only other allowable value is *YES which indicates that a message should be automatically issued. The message will appear on line 22/24 of the next screen format presented to the user or on the job log of a batch job.

LOCK

Specifies whether or not the record should be locked when it is read.

*NO, which is the default value, indicates that the record should not be locked.

*YES indicates the record should be locked. It is the responsibility of the user to ensure that the record is released at some future time.

Note: LOCK(*YES) performs a record level lock. It may exhibit intra and inter operating system behavioral variations (e.g. commitment control locking multiple records; default wait times). User's are advised to investigate the development of proper and complete "user object" locking protocol by using the LOCK_OBJECT Built-In Function.

RETURN_RRN

Specifies the name of a field in which the relative record number of the record just selected should be returned.

Any field nominated in this parameter must be defined within the function or the LANSA data dictionary and must be numeric.

Note: The value returned by this parameter, when OPTIONS(*BLOCKnnn) is used, is largely useless, as it represents the relative record number of the last record in the block of records just read, which may not be the number of the record currently being processed by the SELECT/ENDSELECT loop.

For further reference refer to Load Other File in the Visual LANSA Developers Guide.

OPTIONS

Specifies from 1 to 4 special options that can be used when processing records from the file. Allowable special options are:

*BACKWARDS: Indicates that the records should be processed in reverse order to that which would normally be used. Normally records are read in the order of the key specified in the FROM_FILE parameter.

When the *BACKWARDS option is used the records are read in reverse order. Backwards processing by sequential, full or partial key is supported (even though it is not supported by some other high level languages such as RPG). Generic key processing backwards is also supported (but may be hard to effectively implement).

*STARTKEY: Indicates that the key(s) nominated in the WITH_KEY parameter should only be used to establish the start position for the first read operation.

The first record read will be the first one that has a key greater than or equal to the key value(s) nominated in the WITH_KEY parameter. All subsequent records are then processed with no regard to the WITH_KEY values. In this situation the SELECT loop normally has to be terminated by program logic or by using the special option *ENDWHERE.

*ENDWHERE: Specifies that if the condition specified in the WHERE parameter is found to be false (that is, not true) then the SELECT loop should terminate. Control is then passed to the position in the program nominated by the END_FILE parameter.

Normally a SELECT loop only terminates when all records that could match the selection criteria have been read and examined for possible selection and processing by the SELECT loop. When the *ENDWHERE option is used, the first time a record is read (or some other condition occurs) that causes the WHERE condition to be false, the SELECT loop is terminated.

*ENDWHERESQL: allows you to handle SELECT commands in the most appropriate manner according to the WHERE condition.

SQL (using ODBC) doesn't handle table operations the same way as native I/O on IBM i . To enhance the performance of the SELECT command for different tables (SQL and native I/O), this value supersedes *ENDWHERE.

This new value is applied by selecting the force *ENDWHERESQL option in the Partition definition tab. This partition option signals to LANSA that ALL *ENDWHERE options in SELECT commands are to be interpreted as though *ENDWHERESQL had been coded. As code is updated, or new code is written, it is recommended that the SELECT commands are changed to use this new option where it is appropriate.

* BLOCKnnn: Specifies that the records selected from the file are to be read in blocks to reduce the number of real database I/O operations being performed.

     Used properly, this option can substantially improve the performance of a SELECT/ENDSELECT loop.

     However, LANSA and IBM i conditions apply to using it:

• The 'nnn' component of the *BLOCKnnn parameter value specifies the number of records read in each block. The allowable values for 'nnn' are 010, 020, 030, 040, 050, 060, 070, 080, 090, 100, 150, 200, 250, 300, 400 or 500. The IBM recommended value is as many records as will fit into a 32K block.

• The use of *BLOCKnnn implies the use of *DBOPTIMISE, regardless of whether or not *DBOPTIMISE is actually specified in a FUNCTION command.

• The SELECT command must not have a WITH_KEY parameter. The use of the WITH_KEY parameter causes file cursor positioning operations that disable the blocking logic.

• No other I/O operations must be performed on the file specified in the FROM_FILE parameter anywhere else in the function.

• The SELECT/ENDSELECT loop must be executed once and only once in the programs invocation. Subsequent attempts to (re)execute the SELECT/ENDSELECT loop will cause unpredictable results because the file cursor will not be (re)positioned to the start of the file. If this feature is required, CLOSE the file before attempting to (re)execute the SELECT/ENDSELECT loop.

• The file must not be left open by option *KEEPOPEN or have been opened (or left open) by some other function / program, including this one. If the file is already open the current location of the file cursor is unpredictable. If in doubt, use the CLOSE command to close the file first.

• The relative record number returned by the RETURN_RRN parameter is meaningless when OPTIONS(*BLOCKnnn) is used as it represents the number of the last record read in the current block, which may not be the record being processed by the SELECT/ENDSELECT loop.