db2 for i:在 in 子句中的 sql 过程中传递包含逗号分隔字符串的 varchar [英] db2 for i : passing a varchar containing comma separated string in an sql procedure in in clause

问题描述

我在 java 中有一个包含逗号分隔字符串的字符串.我将其传递给在 java 中调用的 sql 过程...这里是 java 字符串的例子:

i have a string containing comma separated strings in java.. which i m passing to a sql procedure being called in java ... here is example of java string:

    String codeString = "'232/232','34fd/34//'";

sql db2 for i 中的代码:

    create procedure history (in id varchar (3), in code varchar (2000))
   ......
   ......
   begin
   insert into table1
   select date_from, date_to, code
   from table2 
   where table2.serial= id
   and table2.status not in (code); 
   end

此 sql 过程在 table1.code 中插入相同的字符串，但不排除 in 子句中的 table2.status..

this sql procedure is inserting same string in table1.code but it is not excluding table2.status in in clause..

table1.code 中插入的值是 '232/232','34fd/34//'(包括所有单引号和逗号)

value being inserted in table1.code is '232/232','34fd/34//' (including all single quotes and commas)

推荐答案

你在说什么:

and table2.status not in (code);

同说:

and table2.status <> code;

你不是说:

and table2.status not in ('232/232','34fd/34//');

原因是它检查 status 是针对整个 code，而不是 code 的某些部分.code 中的逗号分隔列表不被解析.实际上没有变量被解析.他们被完整地对待.如果您希望 in 谓词中有多个值，则需要多个文字值、多个变量或它们的某种组合.

The reason is that it is checking status against the whole of code, not some part of code. The comma separated list in code is not parsed. In fact no variables are parsed. They are taken whole. If you want multiple values in the in predicate, you need either multiple literal values, multiple variables, or some combination of that.

它适用于插入，因为它只是将 code 的整个值插入到 table1 的列中.无需解析.

It works for the insert because it is just inserting the whole value of code into the column of table1. No parsing required.

另一个选项，因为您在一个过程中，所以您自己将逗号分隔的列表解析为一个字符串数组(这里不向您展示)，然后使用集合派生表将该数组转换为一个表您可以像这样在 in 谓词中使用:

The other option, since you are in a procedure is to parse the comma separated list yourself into an array of strings (not going to show you that here), then use a collection derived table to convert the array to a table that you can use in the in predicate like this:

status not in (select s.string from UNNEST(stringArray) as s(string))

这篇关于db2 for i:在 in 子句中的 sql 过程中传递包含逗号分隔字符串的 varchar的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！