SELECT … INTO Statement | VantageCloud Lake - SELECT ... INTO - Teradata Vantage

Selects at most one row from a table and assigns the values in that row to host variables in an embedded SQL application or to local variables or parameters in stored procedures.

ANSI Compliance

SELECT ... INTO is ANSI/ISO SQL:2011-compliant.

Required Privileges

To select data from a table, you must have SELECT privilege on that table.

To select data through a view, you must have the SELECT privilege on that view. Also, the immediate owner of the view (that is, the database in which the view resides) must have SELECT WITH GRANT OPTION privileges on all tables or views referenced in the view.

Invocation

Executable.

Stored procedures and embedded SQL.

Stored Procedure Syntax

[ with_[recursive]_modifier ] { SELECT | SET }
  [ ALL | DISTINCT ] select_list
  INTO { local_variable_name | parameter_name } [,...]
  [ from_clause ]
  [ where_clause ]

Embedded SQL Syntax

[ with_[recursive]_modifier ] { SELECT | SET }
  select_list
  INTO into_spec [,...]
  [ from_clause ]
  [ where_clause ]

Syntax Elements

into_spec

[:] host_variable_name [ [INDICATOR] :host_indicator_name ]

with_[recursive]_modifier

A recursive query that provides a way to search a table using iterative self-join and set operations if WITH RECURSIVE is used.

The nonrecursive WITH request modifier is similar to a derived table.

select_list

An asterisk (*) or a comma-separated list of valid SQL expressions.

The select list can contain instances of the DEFAULT function, but must not contain aggregate or ordered analytical functions.

host_variable_name

The name of the host variable into which the selected data is to be placed.

host_indicator_name

The name of the host indicator variable.

from_clause

A clause listing the tables or views referenced by the SELECT.

where_clause

Narrows a SELECT to rows that satisfy a specified conditional expression.

The WHERE clause can contain the DEFAULT function as a component of its predicate.

local_variable_name

The name of the local variable declared in the stored procedure into which the SELECTed data is to be placed.

You cannot use stored procedure status variables here.

parameter_name

The name of the stored procedure parameter into which the SELECTed data is to be placed.

Only output parameters (INOUT and OUT type) can be specified.

Usage Notes

• Rules for Using a Colon Character
  Following are the rules for using a colon character in embedded SQL:

  • Pad characters preceding and following a colon character are optional.
  • A prepending colon character for host_variable_name is optional.
  • A prepending colon character must precede a host_indicator_name.
  Following are the rules for using a colon character in stored procedures:

  • A prepending colon character preceding a local_variable_name is optional.
  • A prepending colon character preceding a param_name is optional.
• Rules for Stored Procedures
  The following must be specified in the given order:

  1. WITH [RECURSIVE] request modifier
  2. SELECT clause
  3. INTO list
  4. FROM clause

  Any other element between the INTO list and FROM causes an error. You can specify all other clauses in the statement in any order.

  You have to specify the column list explicitly in the SELECT clause. The SELECT * syntax is not allowed in stored procedures.

  The SELECT privilege is required on all tables specified in the FROM clause and in any subquery contained in the query specification, or on the databases containing the tables.

  UNION, INTERSECT and MINUS clauses are not supported in the SELECT ... INTO statement.

  The number of columns specified by the select list must match the number of local variable and parameter specifications.

  The local variable or parameter and the corresponding column in the returned data must be of compatible data type. Vantage performs implicit conversions for DateTime data types when the data type of the local variable or parameter differs from the corresponding column data type.

  If an error or failure occurs for the statement, normal exception condition handling takes place.

  The SELECT ... INTO statement is typically expected to return at most one row. One of the following actions is taken after running the statement:

  Rows Returned	Stored Procedure Status Variables Values	Meaning
  More than one	SQLCODE = 7627 SQLSTATE = '21000' ACTIVITY_COUNT = number of rows found	Exception condition (failure in Teradata session mode, error in ANSI session mode) A specific condition handler or a generic handler can be specified to handle this condition. The values of local variables and parameters do not change.
  None, without execution warning	SQLCODE = 7632 SQLSTATE = '02000' ACTIVITY_COUNT = 0	Completion condition other than successful completion. A specific condition handler can be specified to handle this completion condition. The values of local variables and parameters do not change.
  None, with execution warning	SQLCODE = warning code SQLSTATE = SQLSTATE value corresponding to warning ACTIVITY_COUNT = 0	Completion condition other than successful completion. A specific condition handler can be specified to handle this completion condition. The values of local variables and parameters do not change.
  Exactly one, without execution warning	SQLCODE = 0 SQLSTATE = SQLSTATE '00000' ACTIVITY_COUNT = 1	Fetched values are assigned to the local variables and parameters. This is a successful completion. A specific handler cannot be specified to handle this.
  Exactly one, with execution warning	SQLCODE = warning code SQLSTATE = SQLSTATE value corresponding to warning ACTIVITY_COUNT = 1	Fetched values are assigned to the local variables and parameters. This is a completion condition other than successful completion. A specific handler can be specified to handle this condition.

• Rules for Embedded SQL

  The SELECT privilege is required on all tables specified in the FROM clause and in any subquery contained in the query specification, or on the database set containing the tables.

  The number of columns specified by select_list must match the number of host variable specifications.

  Values are assigned to the host variables specified in the INTO clause in the order in which the host variables were specified. A value is assigned to SQLCODE last.

  The main host variable and the corresponding column in the returned data must be of the same data type group, except that if the main host variable data type is approximate numeric, the temporary table column data type can be either approximate numeric or exact numeric.

  If the temporary table contains zero rows (is empty), the value +100 is assigned to SQLCODE and no values are assigned to the host variables specified in the INTO clause.

  If exactly one row of data is returned, the values from the row are assigned to the corresponding host variables specified in the INTO clause.

  If more than one row of data is returned, the value -810 is assigned to SQLCODE, and no values are assigned to the host variables specified in the INTO clause.

  If an error occurs in assigning a value to a host variable, one of the values -303, -304, or -413 is assigned to SQLCODE, and no further assignment to host variables occurs.

  If a column value in the returned data is NULL and a corresponding indicator host variable is specified, the value -1 is assigned to the indicator host variable and no value is assigned to the main host variable. If no corresponding indicator host variable is specified, the value -305 is assigned to SQLCODE and no further assignment to host variables occurs.

  If a column value in the returned data is NOT NULL and a corresponding indicator host variable is specified, the indicator host variable is set as follows:

  • If the column and main host variable are of character data type and the column value is longer than the main host variable, the indicator host variable is set to the length of the column value.
  • In all other cases, the indicator variable is set to zero.

  If no other value is assigned to SQLCODE, the value zero is assigned to SQLCODE.

  Column values are set in the corresponding main host variables according to the rules for host variables.

  You cannot run SELECT ... INTO as a dynamic SQL statement.

  SELECT ... INTO supports browse mode SELECT operations for queue tables.

• Rules for Using the DEFAULT Function With SELECT Statements
  • The DEFAULT function takes a single argument that identifies a relation column by name. The function evaluates to a value equal to the current default value for the column. For cases where the default value of the column is specified as a current built-in system function, the DEFAULT function evaluates to the current value of system variables at the time the request runs.

    The resulting data type of the DEFAULT function is the data type of the constant or built-in function specified as the default, unless the default is NULL. If the default is NULL, the resulting data type is the data type of the column or expression for which the default is being requested.

  • The DEFAULT function has two forms, DEFAULT and DEFAULT (column_name). When no column name is specified, the system derives the column based on context. If the column context cannot be derived, the request aborts and an error is returned to the requestor.
  • You can specify a DEFAULT function with a column name in the select list of a SELECT statement. The DEFAULT function evaluates to the default value of the specified column.
  • You cannot specify a DEFAULT function without a column name in the expression list. The system aborts the request and returns an error to the requestor.
  • If you specify a SELECT statement without a FROM clause, the system returns a single row with the default value of the column.

    This is similar to the existing TYPE function.

  • When the column passed as an input argument to the DEFAULT function does not have an explicit default value associated with it, the DEFAULT function evaluates to null.

Example: Recursive Query

The following example shows a recursive query used inside a client application:

EXEC SQL
      WITH RECURSIVE Reachable_From (Source, Destin, mycount)AS
   (      SELECT Root.Source, Root.Destin, 0 as mycount
          FROM Flights Root
          WHERE Root.Source = ‘Paris’
      UNION ALL
          SELECT in1.Source, out1.Destin, in1.mycount + 1
          FROM Reachable_From in1, Flights out1
          WHERE in1.Destin = out1.Source
          AND in1.mycount <= 100
      )
      SELECT Source, Destin
      INTO :intosource INDICATOR :indvar1
      :intodestin INDICATOR: indvar2
      FROM Reachable_From;
END-EXEC

In this example, the host variables intosource and intodestin and indicator variables indvar1 and indvar2 are being used in the final SELECT of the recursive query. These variables cannot be used inside the recursive query definition.

Related Information

• WITH and WITH RECURSIVE modifiers, see WITH Modifier.
• Authorization details, see CALL.
• from_clause, see FROM Clause.
• Performing implicit conversions for DateTime data, see Implicit Type Conversions.
• SELECT ... INTO supporting browse mode SELECT operations for queue tables, see CREATE TABLE (Queue Table Form).
• DEFAULT function, see DEFAULT.