Passing comma separated vlaues to DB2 for IN Clause

Question

I have a table valued function in DB2 which takes AddressCode as one of the parameters. AddressCode is of type Char(50).

When I pass a single string value from my C# code like AddressCode = "ABE001". It works fine, however when i pass in two values

e.g.,

string mAddressCode = "M0044";
string bAddressCode = "B0608";    
string formattedAddressCode = mAddressCode + bAddressCode;

I get the following exception

$exception  {"ERROR [42601] [IBM][AS] SQL0104N  An unexpected token \"MUL0044\" was found following \"\".  Expected tokens may include:  \") ,\"."} IBM.Data.DB2.Core.DB2Exception

My where clause in Table Valued function looks like this

WHERE A . EMPSFT = 'Y' AND A . EMUKCD in ADDRESScODE

I would like to find out how to format the string to be used for IN Clause in DB2.

The following where clause works

WHERE A . EMPSFT = 'Y' AND A . EMUKCD in ('M0044','B0608')

The SQL script is passed to DB2 like so

  string formattedAddressCode =  string.Join(",", addressCode.Split(',').Select(x => $"'{x}'"));

  string sql = $"SELECT * FROM TABLE ( ABELIBLE.TVFBOEGETSHIPMENTS ( '{formattedStartDate}', '{formattedEndDate}' , '{formattedAddressCode}') )";

sql string looks like this

SELECT * FROM TABLE ( ABELIBLE.TVFBOEGETSHIPMENTS ( '2020-07-06', '2020-08-05' , ''M0044','B0608'') )

You've got single quotes around the entire thing, so it will be `"'M0044, B0608//'"`. You need to have `"','"` instead of `","` between them and it should end with `"'"`, not `"//'"` — juharr, Aug 05 '20 at 13:35
There may be other problems as well, but we'd need to see the code you use to create your entire query string first. — juharr, Aug 05 '20 at 13:39
If the error message is complaining about the M0044 being unexpected - that may indicate you are also missing the brackets/parentheses -('M0044','B0608') — PaulF, Aug 05 '20 at 13:44
It is useful for debugging to display (or log to a file in debugging mode) the FULL SQL query before submitting it to the database for execution. — mao, Aug 05 '20 at 14:38
@mao - I have updated my question now with the sql query which is being passed in from c#. Its almost like the quote are not in the right place. — Abe, Aug 05 '20 at 15:04
For the IN list, the elements are delimited by ' (single-quote) and separated by comma. In your list, do you mean M0044 and B0608 to be __two__ elements or a single element, and do quotes occurr in their values? — mao, Aug 05 '20 at 15:06
@mao - Thanks for your comment. I meant two elements. As in In('M0044','B0608'). Hope this answers your question. — Abe, Aug 05 '20 at 15:40
If you pass a number of tokens in a single string, you must tokenize it. Look [here](https://stackoverflow.com/a/61540876/10418264), for example. — Mark Barinstein, Aug 05 '20 at 17:19

score 0 · Accepted Answer · answered Aug 06 '20 at 11:08

I hope this helps guys. I managed to fix it by changing my where clause to

    WHERE
            A . EMPSFT = 'Y'
            AND A . EMUKCD IN (
                SELECT regexp_substr(ADDRESSCODE,
                '[^,]+',
                1,
                LEVEL)
            FROM
                SYSIBM.SYSDUMMY1
            CONNECT BY
                regexp_substr(ADDRESSCODE,
                '[^,]+',
                1,
                LEVEL) IS NOT NULL)

Basically you need to do something like this

select regexp_substr('SMITH,ALLEN,WARD,JONES,sandhya, abe','[^,]+', 1, level) from SYSIBM.SYSDUMMY1
  connect by regexp_substr('SMITH,ALLEN,WARD,JONES, sandhya, abe', '[^,]+', 1, level) is not null;

I got this from the following link Oracle link but modified it to suit DB2

Passing comma separated vlaues to DB2 for IN Clause

1 Answers1