FETCH

This SQL statement can be executed in the following ways:	Through the CA Datacom Datadictionary Interactive SQL Service Facility (interactive)	In an application program prepared using a CA Datacom/DB SQL Preprocessor (embedded)	By using CA Dataquery (SQL & Batch Modes)
FETCH		YES

Note: YES indicates a valid execution method for this statement. For information about the access rights required to execute this statement, see the CA Datacom/DB Database and System Administration Guide.

The FETCH statement positions a cursor on the specified row of its result table and assigns the value of that row to any specified host variables. You can use multiple FETCH statements referencing the same cursor. The host variables in the INTO list are matched by position to SELECT list expressions. The INTO list may FETCH a leading subset of the SELECT list expressions. However, unless the plan has SQLMODE=DB2, the FETCH statement that appears first in the host program must have the same or greater number of host variables. Also, although each FETCH statement may reference different host variables, unless the plan has SQLMODE=DB2, they must have the same data type, length, precision, and scale.

Following is the syntax diagram for the FETCH statement:

►►─ FETCH ─┬───────────────┬──┤ fetch-orientation ├──┬──────────┬─ cursor-name ─►
           ├─ INSENSITIVE ─┤                         └─ FROM ◄ ─┘
           └─ SENSITIVE ───┘
►──┬──────────────────────┬─────┬───────────────────────────────────┬──────────►◄
   └─┤ single-row-fetch ├─┘     ├        ┌─ , ─────────────┐        ┤
                                ├─ INTO ─▼─ host-variable ─┴────────┤
                                └─ USING DESCRIPTORdescriptor-name ─┘

Expansion of fetch-orientation
├──┬── BEFORE ──────────┬──────────────────────────────────────┤
   ├── AFTER ───────────┤
   └─┤ row-positioned ├─┘

Expansion of row-positioned

├──┬── NEXT ────────────────────────────┬───────────────────────┤
   ├── PRIOR ───────────────────────────┤
   ├── FIRST ───────────────────────────┤
   ├── LAST ────────────────────────────┤
   ├── ABSOLUTE──┬─ host-variable ────┬─┤
   ├             └─ integer-constant ─┘ ┤
   └── RELATIVE ─┬─ host-variable ────┬─┘
                 └─ integer-constant ─┘

Expansion of single-row-fetch

├──┬───────────────────────────────────┬─────────────────────────┤
   ├        ┌─ , ─────────────┐        ┤
   ├─ INTO ─▼─ host-variable ─┴────────┤
   └─ INTO DESCRIPTOR descriptor-name ─┘

Note 1: Whether INSENSITIVE or SENSITIVE is the default in the FETCH statement depends on the sensitivity of the cursor specified in the DECLARE CURSOR statement. If INSENSITIVE was specified in DECLARE CURSOR, INSENSITIVE is the default in the FETCH statement. If SENSITIVE was specified in DECLARE CURSOR, SENSITIVE is the default in the FETCH statement.

Note 2: If SENSITIVE or INSENSITIVE is specified in the FETCH statement, single-row-fetch must also be specified.

Note 3: If BEFORE or AFTER is specified, do not specify SENSITIVE, INSENSITIVE, or the accompanying single-row-fetch.

Note 4: For a scrollable cursor, all FETCH statements with the single-row-fetch must specify the same number of columns.

Note 5: INTO and USING are optional, as shown, if a SCROLL CURSOR is being used. For non-SCROLL CURSOR operations, however, specifying either INTO or USING is required.