SELECT AND CONSUME … INTO | Teradata Vantage - SELECT AND CONSUME … INTO - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
xqq1557098602407.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™

Purpose

Selects data from the row with the oldest insertion timestamp in the specified queue table, deletes the row from the queue 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.

Invocation

Executable.

Stored procedures and embedded SQL.

Syntax: Stored Procedures Only

{ SELECT | SET } AND CONSUME TOP 1 select_list
   INTO into_spec [,...] FROM queue_table_name
into_spec
[:] { local_variable_name | parameter_name }

Syntax: Embedded SQL Only

{ SELECT | SET } AND CONSUME TOP 1 select_list
   INTO into_spec [,...] FROM queue_table_name
into_spec
[:] host_variable_name [ [INDICATOR] :host_indicator_name ]
select_list
An ASTERISK character (*) or a comma-separated list of valid SQL expressions.
If select_list specifies *, all columns from the queue table specified in the FROM clause are returned.
The select list must not contain aggregate or ordered analytical functions.
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.
queue_table_name
The name of a queue table that was created with the QUEUE option in the CREATE TABLE statement.
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.
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.

ANSI Compliance

SELECT AND CONSUME … INTO is a Teradata extension to the ANSI/ISO SQL:2011 standard.

Authorization

To execute a SELECT AND CONSUME … INTO from a queue table, you must have the SELECT and DELETE privileges on that table.

Attributes of a Queue Table

A queue table is similar to an ordinary base table, with the additional unique property of behaving like an asynchronous first-in-first-out (FIFO) queue.

The first column of a queue table contains Queue Insertion TimeStamp (QITS) values. The CREATE TABLE statement must define the first column with the following data type and attributes:

TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)

The QITS value of a row indicates the time the row was inserted into the queue table, unless a different, user-supplied value is inserted.

Using a Colon Character in Embedded SQL

In embedded SQL, blanks before and after a colon character are optional; use of the colon character before host_variable_name is optional; a colon character must precede a host_indicator_name.

Rules for Embedded SQL

The same rules that apply to SELECT … INTO apply to SELECT AND CONSUME … INTO.

Related Topics

For more information about:
  • queue_table_name, see “CREATE TABLE (Queue Table Form)” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
  • Usage notes, information on transaction processing, locks, and restrictions, see “SELECT AND CONSUME” in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
  • The rules that apply to SELECT … INTO and SELECT AND CONSUME … INTO, see Rules for Embedded SQL.