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

SQL Functions, Expressions, and Predicates

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-30
dita:mapPath
tpt1555966086716.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1145
lifecycle
previous
Product Category
Teradata Vantage™

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