Variable in comma separated in the IN CLAUSE of SP of DB2 for Z/OS

Question

I have a SP in which I have a In clause like mentioned below value1 is a Int,Variable1 is varchar Suppose SP started Variable1=(value1,value2,value3)--getting from another table

Select * from tableA where Column1 in (Variable1).

The just above statement is not working ,needed a work around for this ,Please help

You may find some help in http://stackoverflow.com/questions/27246620/pass-array-as-parameter-in-db2-stored-procedure Another trick would be to pass an XML list and use `XMLTABLE` to perform a `JOIN` — Stavr00, Apr 07 '17 at 14:33

score 0 · Answer 1 · answered Apr 07 '17 at 15:29

Here is a small example of using an XML list to select an arbitrary number of values:

SELECT * FROM "tableA"
WHERE "Column1" IN (
SELECT * FROM
XMLTABLE('$X/set/row' PASSING XMLPARSE('
<set>
<row item="1"/>
<row item="2"/>
<row item="3"/>
<!-- add as many "row" as you need here -->
</set>
')
AS "X"
COLUMNS 
"item" INT PATH '@item'
) AS X
)

Then you can parameterize the query, where ? is a parameter of type XML

SELECT * FROM "tableA"
WHERE "Column1" IN (
  SELECT * FROM 
  XMLTABLE('$X/set/row' PASSING ? AS "X" COLUMNS "item" INT PATH '@item') AS X
)

Variable in comma separated in the IN CLAUSE of SP of DB2 for Z/OS

1 Answers1