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?
Discussion posts and replies are publicly visible
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, ",")
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
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...
I have the same question. You should use the Query DB node only in rare and exceptional circumstances.
hi jessicaw7755 agree to Mike Schmitt , Could you create a stored procedure in db and execute stored procedure smart service in this case?
Hi Mike, just trying to update one field for an array of data types. I am trying to avoid querying all the data and storing it in process to keep process size small and avoid heavy queries
Hi, I tried that, it didn't work
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.
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.