The SELECT statement executes one or more of several courses of action based on one or more conditions. The SELECT statement has three formats.
This statement has the following format:
The Format 1 of the SELECT statement selects the set of statements that follows the first WHEN value that matches the value of the select‑subject. Only the set of statements that follows the first matching value executes.
SELECT select_subject
{numeric_expression } [ {numeric_expression} ]
WHEN {alpha_expression } [OR {alpha_expression } ]...
{NULL } [ {NULL ] ]
statements
{numeric_expression } [ {numeric_expression} ]
WHEN {alpha_expression } [OR {alpha_expression } ]
... {NULL } [ {NULL } ]
statements
.
.
.
[ {NONE } ]
[WHEN {OTHER } ]
[ statements ]
[WHEN ANY ]
[ statements]
{ENDSEL }
{ENDSELECT }
The identifier of a numeric or alphanumeric expression whose value determines the action selected.
Specifies a possible value (or values). If the value specified in the WHEN clause matches the value of select_subject, the statements that follow the WHEN clause execute. In Format 1, only the statements that follow the first WHEN condition to test true execute, optionally followed by a WHEN ANY clause.
The value that compares select_subject. You can combine expressions, including NULL, using OR.
An optional postscript that specifies that when none of the values listed matches the value of select_subject, the statements following the WHEN OTHER or WHEN NONE execute. The reserved words OTHER and NONE are interchangeable.
An optional postscript that specifies that when any WHEN value matches the value of select_subject, the statements that follow the WHEN ANY execute in addition to the statements that follow the equal case.
Reserved words that terminate the SELECT construct. If SELECT constructs are nested, the most recent unterminated SELECT construct is terminated by the first occurrence of ENDSEL or ENDSELECT. Each SELECT in a nested SELECT construct must have a corresponding ENDSEL or ENDSELECT.
Note: A select_subject that evaluates to the null value matches NULL in a WHEN clause. It does not match an expression in a WHEN clause that also evaluates to the null value.
Example
SELECT TRANS_CODE WHEN 'A' DO ADD_RECORD_PROC WHEN 'D' DO DEL_RECORD_PROC WHEN 'P' DO PURCHASE_PROC WHEN 'R' DO RECEIPT_PROC WHEN ANY DO LOG_TRANS WHEN OTHER DO INVALID_CODE ENDSEL
Format 2 of the SELECT statement selects the statements that follow the first true condition in a series of conditions. Only the statements that follow the first true condition execute.
SELECT [FIRST [ACTION]]
WHEN condition
statements
[WHEN condition]
[statements ] ...
[ {NONE }]
[WHEN {OTHER }]
[ statements ]
[WHEN ANY ]
[ statements ]
{ENDSEL }
{ENDSELECT }
Optional reserved words that you can add for readability.
Specifies a condition. (For an explanation of valid conditions, see the "Procedure Definition Language Concepts and Language Elements" chapter.) If this is the first true condition in the SELECT, only the statements that follow it execute, followed by a WHEN ANY clause if one is specified. If more than one condition is true, only the statements associated with the first condition execute.
An optional postscript that specifies that, when none of the previously specified WHEN conditions listed is true, the statements that follow the WHEN OTHER or WHEN NONE execute. You can use the reserved words OTHER and NONE interchangeably.
An optional postscript that specifies that, when any previously specified WHEN condition in the SELECT is true, the statements that follow the WHEN ANY execute in addition to the statements that follow the first true condition.
Reserved words that terminate the SELECT construct. If SELECT constructs are nested, the most recent unterminated SELECT construct is terminated by the first occurrence of ENDSEL or ENDSELECT. Each SELECT in a nested SELECT construct must have a corresponding ENDSEL or ENDSELECT.
Note: If the WHEN condition evaluates as unknown, the statements in the WHEN clause do not execute. The WHEN NONE clause executes if all WHEN conditions are unknown or false.
Examples
SELECT FIRST ACTION
WHEN TOTAL_CHARGE > 250.00
DO LARGE_PURCH
WHEN CUSTOMER_CODE = 'P'
DO PREFERRD_CUSTMR
WHEN TOTAL_CHARGE < 50.00
DO SMALL_PURCHASE
WHEN OTHER
DO NO_DISCOUNT
ENDSEL
The following procedure is equivalent to the format 1 example.
SELECT FIRST
WHEN TRANS_CODE = 'A'
DO ADD_RECORD_PROC
WHEN TRANS_CODE = 'D'
DO DEL_RECORD_PROC
WHEN TRANS_CODE = 'P'
DO PURCHASE_PROC
WHEN TRANS_CODE = 'R'
DO RECEIPT_PROC
WHEN ANY
DO LOG_TRANS
WHEN OTHER
DO INVALID_CODE
ENDSEL
Format 3 of the SELECT statement selects one or more actions to take, based on all conditions found to be true. This executes each set of statements that follows each true condition.
SELECT EVERY [ACTION]
WHEN condition
statements
[WHEN condition]
[ statements ] ...
[ {NONE } ]
[WHEN {OTHER } ]
[ statements ]
[WHEN ALL ] [ statements]
[WHEN ANY ]
[ statements]
{ENDSEL }
{ENDSELECT}
An optional reserved word that you can add for readability.
Specifies a condition to test. For more information about valid conditions, see the "Procedure Definition Language Concepts and Language Elements" chapter. For every true condition, the statements that follow execute.
An optional postscript that specifies that, when none of the conditions listed is true, the statements that follow the WHEN OTHER or WHEN NONE execute. The reserved words OTHER and NONE are interchangeable.
An optional postscript that specifies that, when all of the conditions are true, the statements that follow the WHEN ALL clause execute in addition to the statements following each true condition.
An optional postscript that specifies that, when any one condition is true, the statements that follow the WHEN ANY execute in addition to the statements that follow the true conditions.
Reserved words that terminate the SELECT construct. If SELECT constructs are nested, the most recent unterminated SELECT construct is terminated by the first occurrence of ENDSEL or ENDSELECT. Each SELECT in a nested SELECT construct must have a corresponding ENDSEL or ENDSELECT.
If the WHEN condition evaluates as unknown, the statements in the WHEN clause do not execute. The WHEN NONE clause executes if all WHEN conditions are unknown or false.
Example
SELECT EVERY ACTION
WHEN NOT ITEM_NUMBER < 499
DO ERROR_1
WHEN NOT (DISCOUNT_CODE = 1 OR 3)
DO ERROR_2 WHEN NOT ($NUMERIC(UNIT_PRICE)
AND UNIT_PRICE > 0)
DO ERROR_3
WHEN ANY
ADD 1 TO EDIT_FAILED
WHEN NONE
ADD 1 TO EDIT_PASSED
WHEN ALL
DO ALL_ERRORS
ENDSEL
|
Copyright © 2015 CA Technologies.
All rights reserved.
|
|