SELECT … INTO | Teradata Vantage - SELECT ... INTO - Teradata Vantage - Analytics Database

SQL Stored Procedures and Embedded SQL
Deployment
VantageCloud
VantageCore
Edition
VMware
Enterprise
IntelliFlex
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2023-10-30
dita:mapPath
frc1628111662093.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
rjx1472253414573
lifecycle
latest
Product Category
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.

For stored procedures, the local variables and parameters in the select and INTO lists can be UDTs, except VARIANT_TYPE UDTs.

You must have the UDTUSAGE privilege on any local variable or parameter that has a UDT data type.

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.
Stored procedures only:
Vantage performs implicit conversions for DateTime data types. For all other data types, the select list data types must match the INTO clause target list data types.
If the select list data types do not match the INTO clause target list data types, you can specify an explicit CAST to the target type to enable the operation to succeed.
Columns specified in the select and INTO lists can have UDT data types, except for the VARIANT_TYPE UDT data type. The system automatically applies any implicit conversions defined for the UDT if they exist.
The system applies implicit casting of the select list data types from UDTs to predefined data types or from predefined types to UDTs only if a CAST to the target type exists and was created with the AS ASSIGNMENT option specified.
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
A clause narrowing a SELECT to those rows that satisfy a conditional expression that it specifies.
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.
Stored procedures only: A local variable can have a UDT type, except for the VARIANT_TYPE UDT data type.
You must have the UDTUSAGE privilege on any UDT used as a local variable.
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.
Stored procedures only: A parameter can have a UDT type, except for the VARIANT_TYPE UDT data type.
You must have the UDTUSAGE privilege on any UDT used as a parameter.

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 order of specifying the various clauses in SELECT ... INTO is significant in stored procedures. The following must be specified in the given order:
    • WITH [RECURSIVE] request modifier
    • SELECT clause
    • INTO list
    • FROM clause

    If any other element intervenes between the INTO list and FROM, it will result in 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 database(s) containing the tables.

    For stored procedures, you must also have the UDTUSAGE privilege on any UDT used as the data type for any column in the select and INTO lists.

    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.

    For stored procedures, you must have the UDTUSAGE privilege on any UDT used as a local variable or parameter.

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

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

    IF SELECT ... INTO returns … The stored procedure status variables show these values … Which mean …
    more than one row 
    SQLCODE = 7627
SQLSTATE = ‘21000’

    ACTIVITY_COUNT =number of rows found.

    		 an exception condition (a failure in Teradata session mode and 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.
    no rows, without an execution warning 
    SQLCODE = 7632
SQLSTATE = ‘02000’
ACTIVITY_COUNT = 0
    		 a 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.
    no rows, with an execution warning SQLCODE = the warning code.

    SQLSTATE =SQLSTATE value corresponding to the warning.

    ACTIVITY_COUNT = 0.
    		 a 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 row without an execution warning 
    SQLCODE = 0
SQLSTATE = ‘00000’
ACTIVITY_COUNT = 1
    		 the 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 row with an execution warning SQLCODE = the warning code.

    SQLSTATE =SQLSTATE value corresponding to the warning.

    ACTIVITY_COUNT = 1
    		 the 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

    UDTs are not specifically supported.

    Note, however, that UDTs for which tosql and fromsql transforms have been defined can be externally referenced by means of their transform target data types. As a result, embedded SQL applications can use SQL statements that reference UDTs provided that the UDTs have a defined tosql or fromsql transform as appropriate.

    Additionally, the application must send and receive UDT data in the form of its external (non-UDT) data type.

    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 execute 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 is executed.

      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. It can be specified as DEFAULT or 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 that does not also specify a FROM clause, the system always returns a single row with the default value of the column, regardless of how many rows are in the table.

      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_[recursive]_ modifier, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
  • Implicit data type conversions and the CAST function, see Teradata Vantage™ - Data Types and Literals, B035-1143.
  • See the CALL statement in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 for more details on authorization required.
  • Creating a cast and using the AS ASSIGNMENT option, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
  • from_clause, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
  • Performing implicit conversions for DateTime data, see Teradata Vantage™ - Data Types and Literals, B035-1143.
  • SELECT ... INTO supporting browse mode SELECT operations for queue tables, see the information about CREATE TABLE (Queue Table Form) in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
  • the DEFAULT function, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.