Consider there is table with some ids. I want to search the ids that are not available in that table.
For eg: The table
As of now, am writing this query
"Select * from table_name where id in (1991,1992,1993)"
From the result, I am manually finding out that 1993 is missing.
Is there any query to achieve it? That query should directly give me 1993.
You need to split the list of values into rows:
WITH cte AS
SELECT CAST(token AS INT) AS id
FROM TABLE (STRTOK_SPLIT_TO_TABLE(1, '1991,1992,1993', ',')
RETURNS (outkey INTEGER,
token VARCHAR(20) CHARACTER SET UNICODE)
) AS d
SELECT * FROM cte
WHERE NOT EXISTS
( SELECT * FROM tab AS t WHERE t.id = cte.id)