Dynamic WHERE IN statement in Cursor SQL statement

[Tito Burgos]

I'm trying to dynamically build the values of a WHERE IN clause but I
can't seem to get it to work.

I'm new to writing DB2 stored procedures and haven't found any
examples that show what i'm trying to do.

Any help would be appreciated.
Below is a sample procedure that captures what I'm trying to do.

Thanks.

========= sample procedure ==============
CREATE PROCEDURE SP_PROC1(
IN VAR-A CHARACTER(3),
IN VAR-B CHARACTER(10))
--------------------------------------------------------------------------
-- DB2 SQL procedure
--------------------------------------------------------------------------

P1: BEGIN

-- Declare variables
DECLARE v_VAR-C VARCHAR (13);

-- Declare cursors
DECLARE Cursor1 CURSOR WITH RETURN FOR

SELECT COLUMN-A, COLUMN-B
FROM TABLE-A
WHERE
COLUMN-A = VAR-A
AND COLUMN-B IN (v_VAR-C) <= I want this var to contain the list of
items.
--

-- here is where I try to build the values for the WHERE IN clause.
IF VAR-B = 'ABC' THEN
SET v_VAR-C = VAR-B || ',' || 'BCD';
ELSE
SET v_VAR-C = VAR-B;
END IF;

OPEN Cursor1;

--

END P1

[Serge Rielau]

You need to use dynamic SQL.
It works something like this:

DECLARE text VARCHAR(1000);
DECLARE stmt STATEMENT;
DECLARE cur CUSROR FOR stmt;

SET txt = 'SELECT ...';
PREPARE stmt FROM text;
OPEN cur;

--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau

[--CELKO--]

You are missing some basic points of SQL. It is a compiled language,
not an interpreted on the fly. You can do this with dynamic SQL, but
this is a sign of bad programming. It says you don't now what you are
doing until run time AND any random user is better than you. Local
variables are also a sign of bad programming.

Next, we don't like cursors. SQL is a declarative set_oriented
language; Using SQL like a 1950's magnetic tape file system is a
sign of bad programming.

You also need to read the ISO_11179 rules for data element names. You
never put meta_data in a name, use the underscore (the dash was
COBOL).

Ignoring that this is bad SQL, a COBOL programmers writes:

IF var_b = 'abc'
THEN SET local_var_c = var_b || ',' || 'bcd';
ELSE SET local_var_c = var_b;
END IF;

But SQL programmers use expressions and not control flow:

SET local_var_c
= CASE WHEN var_b = 'abc'
THEN var_b || ',' || 'bcd
ELSE var_b END;

See the different mindset?

The SQL idiom for what you are doing is called a long parameter list.
It is just what it sounds like. Read my articles at:

http://www.simple-talk.com/sql/learn-sql-server/values%28%29-and-long-parameter-lists/

It ws written for SQL Server but the code is portable. DB2 can handle
32K parameters, so do not worry about what you pass.

[MarkB]

Hello Tito,

You can try to create generic 'tokenizer' function:
---
create function regexp_tokenize(source varchar(4000), pattern
varchar(128))
returns table (token varchar(4000))
return
select x.t
from xmltable('for $id in tokenize($s, $p) return <i>{string($id)}</
i>'
passing
source as "s"
, pattern as "p"
columns t varchar(4000) path '.') x;
---

and use it in your select statement (I presume that you use ',' symbol
as item separator in your list of values):

SELECT A.COLUMN-A, A.COLUMN-B
FROM TABLE-A A, table(regexp_tokenize(v_VAR-C, ',')) t(token)
WHERE
  A.COLUMN-A = VAR-A
AND A.COLUMN-B=t.token

Regards,
Mark B.