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//'";

i的sql db2中的代码:

    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//');

原因是它正在对整个 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屋！