SQL / DB2 Array in Where declared in with-CodePudding

anyone does know a workaround for querying with parameters/variables for usage in where [...] in functions on db2 v11?

what i tried:

DECLARE @list varchar(23) = '1,2,3,4'
SELECT ...FROM tbl WHERE col IN (@list)

WITH test(val) AS (VALUES(ARRAY['5','9']))
SELECT ... FROM table, test WHERE col ANY(val)

both do not work, first one isn't db2 compatible, the second ones does not work cause he cant split the values.

any ideas or examples?

CodePudding user response：

Try this:

SELECT t.*
FROM tbl t
WHERE EXISTS
(
  select 1
  from xmltable
  (
  'for $id in tokenize($s, ",") return <i>{string($id)}</i>' 
  passing '1,2,3,4' as "s"
  columns 
    tok int path '.'
  ) v
  where v.tok = t.col
);

You may use a parameter marker instead of the string constant 1,2,3,4 as for usual string paramter, if you want to provide such a list of integers as a comma separated string at runtime.

CodePudding user response：

Declare local temporary table and insert your value list in that ?