I'd like a DB2 compatible query for all the values in my own hardcoded list of values that are NOT in a table. This query works well in Microsoft SQL Server
SELECT * FROM
(values (1),(2),(3),(4),(7), (7000000)) as T(ID)
EXCEPT
SELECT ID
FROM ACCOUNT;
I'm aware of this answer SQL - How can I return the IDs from a where clause list that are not in the table? where they suggest using a VALUES clause. Perhaps it works with other versions of DB2, but on mine I get the error "Illegal symbol (" so I'm not sure it likes the VALUES function in my version of DB2.
Db2 v11 for Z/OS does not support the values clause in this context, although Db2 v11 on Linux/Unix/Windows does support it.
Although it's ugly, and there may be a better way, you might try:
SELECT * FROM
(select 1 from sysibm.sysdummy1 union
select 2 from sysibm.sysdummy1 union
select 3 from sysibm.sysdummy1 union
select 4 from sysibm.sysdummy1 union
select 7 from sysibm.sysdummy1 union
select 7000000 from sysibm.sysdummy1 )
EXCEPT
SELECT ID
FROM ACCOUNT;