15.00 - Restrictions on Consume Mode SELECT Requests - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Restrictions on Consume Mode SELECT Requests

The following restrictions apply to SELECT AND CONSUME TOP 1 requests (see SQL Data Manipulation Language for details).

  • You cannot join queue tables with other tables in a SELECT AND CONSUME request.
  • 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 request.
  • You cannot specify aggregate or ordered analytic functions in a SELECT AND CONSUME request.
  • The mandatory TOP n clause cannot be specified for any case other than n=1.
  • SELECT AND CONSUME requests do not support the PERCENT or WITH TIES options for the TOP n clause (see SQL Data Manipulation Language for more information about these options).
  • You cannot specify a SELECT AND CONSUME request in any of the following.

  • Subquery
  • Search condition
  • Logical predicate
  • Set operator