将逗号分隔的列表作为参数传递给 DB2 中十进制字段的 IN 子句

Question

我想将逗号分隔的值列表作为参数传递给查询。但它的节目

在函数"DECFLOAT"的字符串参数中找到无效字符。SQLCODE=-420， SQLSTATE=22018， DRIVER=4.18.60

我的代码如下

CREATE OR REPLACE PROCEDURE CUSTOMER_DET(IN customers Varchar(20))
LANGUAGE SQL
.....
....
..
update customers set status = 'Y' where c_id in (customers );
...
...

Answer 1

您可以通过将 XML 文档解析为动态表来利用PureXML：

CREATE OR REPLACE PROCEDURE CUSTOMER_DET(IN customers XML )
LANGUAGE SQL
. . . 
. . .
update customers set status = 'Y' where c_id in 
( 
 SELECT id 
 FROM XMLTABLE(
 '/customers/customer' PASSING customers 
 COLUMNS id VARCHAR(20) PATH '@id') AS custlist 
)

然后使用此 XML 传递客户 ID

<customers>
<customer id="joecustomer"/>
<customer id="frankcustomer"/>
</customers>

Answer 2

通过使用EXECUTE IMMEDIATE 关键字，我解决了这个问题。

DECLARE STMT VARCHAR(20000);
...
..
SET STMT = 'update customers set status = 'Y' where c_id in ('||CHAR(pen_codes)||')';
EXECUTE IMMEDIATE STMT;

但是我无法将此立即执行用于选择输入查询。

SET STMT = 'select sum(requests) into total_request from customers where c_id in ('||CHAR(pen_codes)||')'