FETCH Statement (fetch_statement) (SAP Library

FETCH Statement (fetch_statement)

The FETCH statement (fetch_statement) assigns the values from the current row in a results table to parameters.

Syntax

Explanation

If the name of a results table is not specified, the fetch statement refers to the last unnamed result table that was generated.

Depending on the search strategy, either all the rows in the results table are searched when the SELECT statement (select_statement) is executed and the results table is physically generated, or each next result table row is searched when a FETCH statement is executed, without being physically stored. This must be taken into account for the time behavior of FETCH statements. Depending on the isolation level selected, this can also cause locking problems with a FETCH, such as return code 500 - Lock request timeout.

Row not Found

Let C be the position in the result table. The return code 100 - Row not found - is output and no values are assigned to the parameters if any of the following conditions is satisfied:

● The results table is empty.

● C is positioned on or after the last results table row, and FETCH or FETCH NEXT is specified.

● C is positioned on or before the first row of the result table and FETCH PREV is specified.

● FETCH is specified with a position that is not within the results table.

FIRST | LAST | NEXT | PREV

● FETCH FIRST or FETCH LAST: the results table is not empty. C is positioned in the first or last row of the results table and the values of this row are assigned to the parameters.

● FETCH or FETCH NEXT: C is positioned before a row in the results table. C is positioned in this row and the values of this row are assigned to the parameters.

● FETCH or FETCH NEXT: C is positioned in a row that is not the last row in the results table. C is positioned directly on the next row and the values in this row are assigned to the parameters.

● FETCH PREV: C is positioned after a row in the results table. C is positioned in this row and the values of this row are assigned to the parameters.

● FETCH PREV: C is positioned in a row that is not the first row in the results table. C is positioned in the immediately preceding row and the values in this row are assigned to the parameters.

<position>

A range of options exists for position specification using the key words POS, ABSOLUTE and RELATIVE.

Position

Regardless of whether an ORDER clause is specified, the rows in a results table are ordered implicitly to make internal numbering possible. You can display this by specifying a rowno column as a selected column. The specification of a position refers to this internal numbering.

● POS (<unsigned_integer>): If a position that is less than or equal to the number of rows in the results table was defined with POS, C is set to the corresponding row and the values of this row are assigned to the parameters. If a position is specified that is greater than the number of rows in the results table, the message 100 – ROW NOT FOUND is output.

● POS (<parameter_spec>)If a position is defined with POS, the parameter specification must denote a positive integer.

ABSOLUTE

ABSOLUTE (<integer>)/ABSOLUTE (<parameter_spec>): Let x be the value of the integer or the parameter specification specified with the position. Let abs_x be the absolute value of x.

● FETCH ABSOLUTE and x is : FETCH ABSOLUTE is the same as a FETCH POS.

● FETCH ABSOLUTE and x=0: the return code 100 – row not found is set.

● FETCH ABSOLUTE and x is negative: C is set after the last row of the result table where FETCH PREV is executed abs_x times. The last row found is the result of the SQL statement. This description refers to the logic and not the flow of the statement. If abs_x is larger than the number of rows in the result table, the message 100 – ROW NOT FOUND is output.

RELATIVE

RELATIVE (<integer>)/RELATIVE (<parameter_spec>): Let x be the value of the integer or parameter specification specified with the position. Let abs_x be the absolute value of x.

● FETCH RELATIVE and x is positive: FETCH NEXT is executed x times from the current position in the result table C.

● FETCH RELATIVE and x=0: corresponds to a FETCH SAME.

● FETCH RELATIVE and x is negative: FETCH PREV is executed abs_x times starting from C. This description refers to the logic and not the flow of the statement. The return code 100 – row not found is output if one of the conditions in the section "row not found" is fulfilled.

SAME

The last row found in the result table is output again.

<parameter_spec>

The parameter specification specified in a position (position) must denote an integer. The remaining parameters in the parameter specification are output parameters. The parameter identified by the n^th parameter specification corresponds to the n^th value in the current results table row. If the number of columns in this row exceeds the number of specified parameters, the column values for which no corresponding parameters exist are ignored. If the number of columns in the row is less than the number of specified parameters, no values are assigned to the remaining parameters. You must specify an indicator name in order to assign NULL values or special NULL values.

Numbers are converted and character strings are truncated or lengthened, if necessary, to suit the corresponding parameters. If an error occurs when assigning a value to a parameter, the value is not assigned and no further values are assigned to the corresponding parameters for this fetch statement. Any values that have already been assigned to parameters remain unchanged.

Let p be a parameter and v the corresponding value in the current row of the result table.

● v is a number: p must be a numeric parameter and v must be within the permissible range of p.

● v is a character string: p must be an alphanumeric parameter.

Additional Information

If FOR REUSE was not specified in the QUERY statement, subsequent INSERT, update, or delete statements that refer to the underlying base table and are executed by the current user or other users can cause multiple executions of a FETCH statement to denote different rows in the result table, even though the same position was specified.

You can prevent other users from making changes by executing a LOCK statement for the entire table or by using the isolation level 2, 3, 15, 20, or 30 with the CONNECT statement or the LOCK option in the query statement.
FOR REUSE must be specified if this is not possible or if users make changes to this table themselves. Changes made in the meantime are not visible in this case.

If a results table that was generated physically contains LONG columns and if the isolation levels 0, 1, and 15 are used, consistency cannot be ensured between the content of the LONG columns and that of the other columns. If the result table was not generated physically, consistency can only be ensured above isolation level 0. For this reason, it is advisable to ensure consistency by using a LOCK statement or the isolation levels 2, 3, 20, or 30.