Declaring a Display Label

You can declare a display label for any column or column expression in the select list of the Projection clause. This temporary name is in scope only while the SELECT statement is executing.

In DB–Access, a display label appears as the heading for that column in the output of the SELECT statement.

In ESQL/C, the value of display_label is stored in the sqlname field of the sqlda structure. For more information on the sqlda structure, see the IBM Informix ESQL/C Programmer's Manual.

If your display label is an SQL keyword, use the AS keyword to clarify the syntax. For example, to use UNITS, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, or FRACTION as display labels, use the AS keyword with the display label. The following statement uses AS with minute as a display label:

If you are creating a temporary table, you must supply a display label for any columns that are not simple column expressions. The display label is used as the name of the column in the temporary table. If you are using the SELECT statement to define a view, do not use display labels. Specify the desired label names in the CREATE VIEW column list instead.

INTO Clause

Use the INTO clause in an SPL routine or an ESQL/C program to specify the program variables or host variables to receive data that SELECT retrieves.

INTO Clause:
 
|--INTO--------------------------------------------------------->
 
   .-,-----------------------------------------------------.
   V                                                       |
>----+-output_var--+-----------------------------------+-+-+----|
     |             |  (1)                              | |
     |             '------+-:indicator_var-----------+-' |
     |                    |  (2)                     |   |
     |                    +--------$indicator_var----+   |
     '-data_structure------------------------------------'

Notes:

ESQL/C only
Informix extension

Element	Description	Restrictions	Syntax
data_ structure	Structure that was declared as a host variable	Data types of elements must be able to store the values that are being selected	Language specific
indicator_ var	Program variable to receive a return code if corresponding output_var receives a NULL value	Optional; use an indicator variable if the possibility exists that the value of the corresponding output_var is NULL	Language specific
output_ var	Program or host variable to receive value of the corresponding select list item. Can be a collection variable	Order of receiving variables must match the order of corresponding items in the select list of Projection clause	Language specific

The INTO clause specifies one or more variables that receive the values that the query returns. If it returns multiple values, they are assigned to the list of variables in the order in which you specify the variables.

If the SELECT statement stands alone (that is, it is not part of a DECLARE statement and does not use the INTO clause), it must be a singleton SELECT statement. A singleton SELECT statement returns only one row.

The number of receiving variables must be equal to the number of items in the select list of the Projection clause. The data type of each receiving variable should be compatible with the data type of the corresponding column or expression in the select list.

For the actions that the database server takes when the data type of the receiving variable does not match that of the selected item, see Warnings in ESQL/C.

The following example shows a singleton SELECT statement in ESQL/C:

EXEC SQL select fname, lname, company_name
   into :p_fname, :p_lname, :p_coname
   where customer_num = 101;

In an SPL routine, if a SELECT returns more than one row, you must use the FOREACH statement to access the rows individually. The INTO clause of the SELECT statement holds the fetched values. For more information, see FOREACH.