Micro Focus Studio Enterprise Edition for Windows > Programming > Data Access > Database Access > Reference > OpenESQL > Embedded SQL Statements > SELECT INTO

SELECT INTO

Retrieves one row of results and assigns the values of the items in a specified SELECT list to the host variables specified in the INTO list.

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 ANSI92ENTRY is set and a singleton SELECT retrieves more than 1 row, SQLCODE will be -811, SQLSTATE will be 21000 and SQLWARN4 will be W. If ANSI92ENTRY is not set, no error or warning conditions will be set.

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