1

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.

mao
  • 11,321
  • 2
  • 13
  • 29
SpareTheRod
  • 476
  • 6
  • 13
  • What is your Db2-server operating-system-platform ? (z/os , i-series, linux/unix/windows) ? Your syntax is valid for Linux/Unix/Windows v11. – mao Feb 27 '18 at 16:12
  • I think it is on IBM Solaris. When I run 'SELECT GETVARIABLE('SYSIBM.VERSION') FROM SYSIBM.SYSDUMMY1' I get DSN11015. – SpareTheRod Feb 27 '18 at 16:21
  • The syntax I'm using on db2 is SELECT t1.id FROM (VALUES (1), (2), (3), (4)) AS t1(id) left join mydb.ACCOUNT a on a.id = t1.id and a.id is null; – SpareTheRod Feb 27 '18 at 16:24
  • 1
    So your Db2-server runs on Z/OS and is it V11 New Function Mode. – mao Feb 27 '18 at 16:28
  • Db2 v11 for Z/OS supports EXCEPT in fullselect. Try `select * from (SELECT * FROM (values (1),(2),(3),(4),(7), (7000000)) as T(ID) ) EXCEPT (SELECT ID FROM ACCOUNT );` – mao Feb 27 '18 at 16:37
  • Thanks. Stll complains "illegal symbol "(". Through experimentation I can see it is complaning about the ( that comes AFTER the values and before the 1. – SpareTheRod Feb 27 '18 at 16:44
  • Seems Fb2 v11 for Z/OS rejects the values clause in this context. Maybe a CTE is needed. – mao Feb 27 '18 at 16:47
  • You can select one row at a time from sysibm.sysdummy1 and union them (instead of using the values clause). Ugly, so there's probably a better way. – mao Feb 27 '18 at 16:54
  • You're right. Post that suggestion as an answer and I'll mark it accepted. I got the below to work: select 1 FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT 2 FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT 3 FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT 4 FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT 5 FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT 6 FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT 7 FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT 8 FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT 9 FROM SYSIBM.SYSDUMMY1 EXCEPT select id from ACCOUNT a; – SpareTheRod Feb 27 '18 at 17:07

3 Answers3

2

Alternatively you can try with a CTA :

WITH
T(ID) AS (VALUES 1,2,3,4,7,7000000)
SELECT ID FROM T
EXCEPT
SELECT ID FROM ACCOUNT;
Benjamin schwarz
  • 89
  • 12
1

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;

mao
  • 11,321
  • 2
  • 13
  • 29
1

Use can use sysibm.sysdummy1:

SELECT t.id
FROM (SELECT 1 as ID FROM sysibm.sysdummy1 UNION ALL
      SELECT 2 FROM sysibm.sysdummy1 UNION ALL
      SELECT 3 FROM sysibm.sysdummy1 UNION ALL
      SELECT 4 FROM sysibm.sysdummy1 UNION ALL
      SELECT 7 FROM sysibm.sysdummy1
     ) as t
EXCEPT
  SELECT ID
  FROM ACCOUNT;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786