16.20 - Queries With Large NOT IN Clauses Can Fail - Advanced SQL Engine - Teradata Database

Queries that contain thousands of arguments within an IN or NOT IN clause sometimes fail.

For example, suppose you ran the following query with 16000 IN clause arguments, and it failed.

   SELECT MAX(emp_num)
   FROM employee
   WHERE emp_num IN(1,2,7,8,...,121347);

A workaround when this problem occurs is to rewrite the query using a temporary or volatile table to contain the arguments within the IN clause.

The following statements allow you to make the same selection, but without failure.

   CREATE VOLATILE TABLE temp_IN_values (
    in_value INTEGER) ON COMMIT PRESERVE ROWS;
   
   INSERT INTO temp_IN_values
   SELECT emp_num
   FROM table_with_emp_num_values;

The new query is as follows:

   SELECT MAX(emp_num)
   FROM employee AS e JOIN temp_IN_values AS en
   ON (e.emp_num = en.in_value);