CODEWITHSUNDEEP
liststored-proceduresdb2
R M
R M

Reputation: 39

Pass a list/array in DB2 stored procedure

SELECT cc.clientid
FROM customer_client cc
GROUP BY cc.clientid
HAVING SUM(CASE WHEN cc.customerid IN (4567, 5678) THEN 1 ELSE 0 END) = COUNT(*) 
   AND COUNT(*) = 2;

I'm calling this query in a Db2 stored procedure where in I've to pass the list of customer id - any working suggestion?

I've tried passing it as below in procedure

CREATE PROCEDURE Find_Client_Customers (
    IN IN_CUSTIDS VARCHAR(1000),
    IN IN_CUST_COUNT INT)

but this is passing the list as a string.

Upvotes: 0

Views: 1619

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12299

You may use a string tokenizer:

create function regexp_tokenize_number(
  source varchar(1024)
, pattern varchar(128))
returns table (seq int, tok bigint)
contains sql
deterministic
no external action
return
select seq, tok
from xmltable('for $id in tokenize($s, $p) return <i>{string($id)}</i>' 
passing 
  source as "s"
, pattern as "p"
columns 
  seq for ordinality
, tok bigint path 'if (. castable as xs:long) then xs:long(.) else ()'
) t;

select *
from table(regexp_tokenize_number('123, 456', ',')) t;

SEQ TOK
--- ---
  1 123
  2 456

In your case:

SELECT cc.clientid
FROM customer_client cc
GROUP BY cc.clientid
HAVING SUM(CASE WHEN cc.customerid IN 
(
select t.tok
from table(regexp_tokenize_number('4567, 5678', ',')) t
) THEN 1 ELSE 0 END) = COUNT(*) 
AND COUNT(*) = 2;

Upvotes: 2

Related Questions