Queries With Large NOT IN Clauses Can Fail

Teradata Vantageā„¢ SQL Functions, Expressions, and Predicates

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K

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);