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 enough to know what Teradata's volatile table is.
I wish It was so I could use exclusion logic Exists to go through the volatile table contents.
So without volatile table , I have a value list
where a.c1 in ( list )
which has like 5K values. Keeping that list in the report is proving expensive. I wondered if it was possible to store this kind of list some place before bringing it in the report. How about CTE and using exists on a CTE , would that achieve similar gains.
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...