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

0 Patty Isecke
Appian Employee
over 11 years ago

Is ac!loggedinusergroup_txt of type multiple?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 helmuta over 11 years ago

yes, it is of type multiple and it is throwing the error if the field contains one value or multiple values.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Patty Isecke
Appian Employee
over 11 years ago

For testing purposes, what happens if you list out the values that would be in ac!loggedinusergroup_txt. Do you still see an error?
Can you test out the same expression in on the database? Do you get a similar error?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 helmuta over 11 years ago

I ran the query in the database without problems but it did not have the ac! variable I had to list the actual name of the category. When I list the names of the categories in the smart service it works just fine. The problems arise when I use the ac! variable.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
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
0 Rully over 11 years ago

Just to add the comment from csteward above, you can also join your multiple input into a single string separated with a common delimiter such as comma (hence use
=',' & joinarray (pv! ArrayInput, ',') & ','

Note: The ac input is set to a single text instead of multiple.

Then you can use INSTR or CHARINDEX to locate the pattern of , [colValue], in your column values. Example in SQL Server:

SELECT * FROM [tableName] WHERE CHARINDEX (',' + [colNameToSearch] + ',' , ac! Input);

Same method if it's in Oracle but using different function, i.e. INSTR
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Chris over 11 years ago

Ah yes, thanks amirula - I left out the input required for INSTR. With my clause above, as the input for ac!MultiItemVariable I use: ="," & joinarray(pv!MultiItemVariable, ",") & ","
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel