Show TOC

FETCH Statement (fetch_statement)Locate this document in the navigation structure

Use

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

Structure
				
<fetch_statement>::=
  FETCH [FIRST | LAST | NEXT | PREV | <position> | SAME]
    [<result_table_name>] INTO <parameter_spec>,...

<position>::=
  POS (<unsigned_integer>)
| POS (<parameter_spec>)
| ABSOLUTE <integer>
| ABSOLUTE <parameter_spec>
| RELATIVE <integer>
| RELATIVE <parameter_spec>

Explanation

If the name of a result 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 result table are searched when the SELECT statement (select_statement) is executed and the result table is physically generated, or each subsequent 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 the 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 one of the following conditions is satisfied:

  • The result table is empty.

  • C is positioned on or after the last result 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 result table.

FIRST | LAST | NEXT | PREV

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

  • FETCH or FETCH NEXT: C is positioned before a row in the result 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 result 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 result 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 result 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.

ABSOLUTE

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

  • FETCH ABSOLUTE and x is positive: FETCH ABSOLUTE corresponds to 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.

POS

Regardless of whether an ORDER clause is specified, the rows in a result 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 result 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 result table, a corresponding message is displayed.

  • POS (<parameter_spec>)

    If a position is defined with POS, the parameter specification must denote a positive integer.

RELATIVE (<integer>)/RELATIVE (<parameter_spec>)

Let x be the value of the integer ( integer) or parameter specification specified with the position. Let abs_x be the absolute amount 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 "Row Not Found" section is satisfied.

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 nth parameter specification corresponds to the nth value in the current result 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.

Further Explanations

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 2, 3, 15, 20 or 30 isolation level with the CONNECT statement or the LOCK option of 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 result table that was generated physically contains LOB columns and if the isolation levels 0, 1 and 15 are used, consistency cannot be ensured between the content of the LOB columns and that of the other columns. If the result table was not generated physically, consistency cannot be ensured on isolation level 0 only. For this reason, it is advisable to ensure consistency by using a LOCK statement or the isolation levels 2, 3, 20 or 30.

More Information