 |
 |
Archives of the TeradataForum
Message Posted: Fri, 11 Jul 2014 @ 20:25:38 GMT
| Subj: | | Re: Inputs in 'IN' Clause |
| |
| From: | | Dieter Noeth |
Pawan Verma wrote:
| | I want to know how many inputs can we pass in an 'IN' clause. Last i know was 1024, has that changed with TD14? I know it can create a
performance issue, but just that its a customer report query and he wants to know how many inputs he can pass. | |
Don't do it.
I've seen a query on a huge table with approx. 10,000 values in an IN-list, which was cancelled after 2:45 hours and consuming > 620,000 CPU
seconds.
If you're on TD14 you can utilize the STRTOK_SPLIT_TO_Table function, e.g.
SELECT * FROM sys_calendar.calendar AS c JOIN
(
SELECT CAST(token AS INT) AS num, tokennum
FROM TABLE (STRTOK_SPLIT_TO_TABLE(1, '41840,1,2,3,4,6,7,8,9', ',')
RETURNS (outkey INTEGER,
tokennum INTEGER,
token VARCHAR(20) CHARACTER SET UNICODE)
) AS d
) AS dt
ON c.day_of_calendar = dt.num
ORDER BY tokennum; -- you can even sort by the IN-list :-)
Dieter
| |
