Optimizing Huge Value List In Teradata Without Volatile Tables

Have a value list like` `where a.c1 in ( list ) ` Then shoving the list in the volatile table is the best way out. However this is being done via cognos & IBM isn't smart enou

Solution 1:

You can pass the list as a string and then split it into a table, e.g. for a list of integers:

where a.c1 in
 (
   SELECT CAST(token AS INT)
   FROM TABLE (STRTOK_SPLIT_TO_TABLE(1, '1,2,3,4,5,6,7,8,9,5000', ',')
        RETURNS (outkey INTEGER,
                 tokennum INTEGER,
                 token VARCHAR(10) CHARACTER SET UNICODE)
              ) AS dt 
 )

Of course the optimizer has no knowledge about the number of rows returned, so better check Explain...