SELECT INTO

Syntax:

>>---EXEC SQL--.-------------------.---.------------.---->
               +-FOR :host_integer-+   +-AT db_name-+

                                        +- ,-+
                                        V    | 
 >----SELECT----.-------------.---INTO--:hvar------------->
                +-select_list-+

 >----select_options----END-EXEC---><

Parameters:

:`host_integer`	A host variable that specifies the maximum number of host array elements processed. Must be declared as PIC S9(4) COMP-5 or PIC S9(9) COMP-5.
`db_name`	The name of a database that has been declared using DECLARE DATABASE.
`select_list`	The portion of the table to retrieve data from.
:`hvar`	One or more host variables to receive the `select_list` items.
`select_options`	One or more statements or other options that can be used with the SQL SELECT statement (for example, a FROM or WHERE clause).

Comments:

A singleton SELECT must contain a FROM clause.

If more columns are selected than the number of receiving host variables, the value of sqlwarn3 is set to 'W'. The data type and length of the host variable must be compatible with the value assigned to it. If data is truncated, the value of sqlwarn1 is set to 'W'.

If a SELECT INTO statement returns more than one row from the database, all rows except the first one will be discarded and sqlwarn4 will be set to "W". If you want to return more than the first row, you should use a cursor. Alternatively, you can specify array items in the INTO clause. The array will be populated up to either the maximum size of the array, the value of host_integer or the number of rows returned, whichever is the smallest.

If SELECT INTO returns more rows from the database than the statement in the application is able to accept, OpenESQL returns the following for each of the specified directives:

CHECKSINGLETON     SQLCODE = -811 SQLSTATE = 21000 SQLWARN4 = W 
NOCHECKSINGLETON   SQLCODE = 0    SQLSTATE = 00000 SQLWARN4 = space 
DIALECT=MAINFRAME  SQLCODE = -811 SQLSTATE = 21000 SQLWARN4 = W  
ANSI92ENTRY        SQLCODE = -1   SQLSTATE = 21000 SQLWARN4 = W

If SELECT INTO returns more rows from the database than the statement in the application is able to accept, and none of these directives are set, then OpenESQL returns:

SQLCODE = +1 SQLSTATE = 21000 SQLWARN4 = W

Note: If any one of the host variables in the INTO clause is an array, then they all must be arrays.

Example:

  ...
     MOVE 99 TO staff-id
     EXEC SQL
        SELECT last_name
           INTO :lname
           FROM staff
           WHERE staff_id=:staff-id
     END-EXEC
     EXEC SQL
        SELECT staff_id
           INTO :staff-id
           FROM staff
           WHERE first_name = 'Phil'
     END-EXEC