Using IN operator on Query Database node

Question

I have this statement as the SQL statement on a Query Database node: UPDATE `ThisTable` SET `Status`= ac!closedStatus WHERE `TableId` in (ac!ListOfTableIds) 
 ac!closedStatus is an Int, ac! ListOfTableIds is a list of ints. 
 When I run the process, the Query Database node errors with this error: There was a problem executing the SQL query. SQL Message : class [Ljava.lang.Long; cannot be cast to class java.lang.Long ([Ljava.lang.Long; and java.lang.Long are in module java.base of loader 'bootstrap') 
 I know I've gotten "IN" working on query database before, I just always forget the trick. Does anyone know it?

Mike Schmitt · Answer

Any particular reason you can't use the WTDS node? I avoid the Query DB node at all costs, because it's not user friendly, not flexible, not easily testable...

Abhishek Karumuru · Answer

hi jessicaw7755 agree to Mike Schmitt , Could you create a stored procedure in db and execute stored procedure smart service in this case?

Mathieu Drouin · Answer

If Record Types are an option, you can do this by simply providing the identifier and field you want to update. All other fields will be ignored. For Data Types, it is possible to do as well, you need to create an additional datatype with the primary key and field you want to update.

Mike Schmitt · Answer

Usually it's just fine to query the data quickly then update it. For special cases (massively larger data loads) you could create a CDT with only the field in question, as Mathieu mentioned already. I'd do this well before trying to do a Query DB node unless for critical-needs special cases where even WTDS won't work.

aryan · Answer

List type variables when called, convert {1,2,3} => 1;2;3. We need to format it using joinarray or something else. Then it will work fine I guess. joinarray( ac!ListOfTableIds, ",")

Soma · Answer

Can you try this 
 UPDATE `ThisTable` SET `Status`= ac!closedStatus WHERE `TableId` INSTR(','||ac!ListOfTableIds||',', ','||TableId||',')>0 
 ac!ListOfTableIds should be joinarray(pv!ListOfTableIds,",") 
 For more information, please refer 
 https://community.appian.com/discussions/f/integrations/1367/i-am-having-issues-using-the-in-operator-of-a-where-clause-in-a-quer

Using IN operator on Query Database node

Top Replies