Restrictions on Consume Mode SELECT Statements - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™
The following restrictions apply to SELECT AND CONSUME TOP 1 statements. See Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
  • You cannot join queue tables with other tables in a SELECT AND CONSUME statement.

    The workaround for this is to perform an INSERT … SELECT operation to copy the rows from the queue table you want to join with another table into a non-queue table, then join that non-queue table with the other table.

    For example:

    DELETE ALL FROM tempTbl;
    INSERT INTO tempTbl
    SELECT AND CONSUME TOP 1 * FROM qTbl;
    SELECT c.name, t.customerId, t.orderId
    FROM tempTbl AS t, customer AS c
    WHERE t.customerId = c.customerId;
  • You cannot specify a WHERE clause in a SELECT AND CONSUME statement.
  • You cannot specify aggregate or ordered analytic functions in a SELECT AND CONSUME statement.
  • The mandatory TOP n clause cannot be specified for any case other than n=1.
  • SELECT AND CONSUME statements do not support the PERCENT or WITH TIES options for the TOP n clause. See Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

You cannot specify a SELECT AND CONSUME statement in a:

  • Subquery
  • Search condition
  • Logical predicate
  • Set operator