Restrictions on Consume Mode SELECT Statements - Advanced SQL Engine - Teradata Database

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;