I am using a Query Database Smart Service to query my database to return a set o

helmuta over 11 years ago

I am using a Query Database Smart Service to query my database to return a set of values for a logged in users category. This works great when the category has one value but when it contains multiple values it fails. Here is the configuration for the node.

query: SELECT site,status FROM `srrequestcdt` where `category` in (ac!loggedinusergroup_txt) AND `team` = "NA"
when my input is one value it works fine but if it is more I get an error "There is a problem with task “Query Database” in the process “Project Dashboard”. There was a problem executing the SQL query. SQL Message : [Ljava.lang.String; cannot be cast to java.lang.String"

Any suggestions? I can provide more information if needed....

Discussion posts and replies are publicly visible

Parents

0 Chris over 11 years ago

This is what I use in Oracle for the 'IN' functionality on a multiple valued variable:
...
and INSTR(ac!MultiItemVariable, ',' || DatabaseFieldNameHere || ',') > 0
..
Works great with 0-N values in your variable if you are using Oracle. Plug in your variable and field name and this should work for you (removing the 'and' of course if that is the only clause). I'm not familiar with any SQL Server equivalent, but you may have luck with CHARINDEX or PATINDEX if you are using MS SQL.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 Chris over 11 years ago

This is what I use in Oracle for the 'IN' functionality on a multiple valued variable:
...
and INSTR(ac!MultiItemVariable, ',' || DatabaseFieldNameHere || ',') > 0
..
Works great with 0-N values in your variable if you are using Oracle. Plug in your variable and field name and this should work for you (removing the 'and' of course if that is the only clause). I'm not familiar with any SQL Server equivalent, but you may have luck with CHARINDEX or PATINDEX if you are using MS SQL.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

No Data