Hi! I´m trying to do the following query with the "query database" smart service:
UPDATE CPF_LINEAS_PEDIDOSET FACTURADA = true where ID_PEDIDO = ac!pedido and MATERIAL = ac!Material
The variable ac!Material can be multiple (f.example Material 1, Material 2). This is because in a previous step in the process model, the user can enter several rows.
Because of using a list of string in the clause "WHERE" (I´m using it as a filter), I think I´m returning the following error:
How can I fix this? Thanks a lot!
Discussion posts and replies are publicly visible
Please don't use the Query Database smart service. You should be querying the data using a regular Query Entity (or Query Record if it's recordType data and not CDT), updating the rows with the relevant new value(s), then writing that back to the DB using the WTDS / Write Record node as appropriate.
In this case I need to use it, I don´t have time to change the process right now! Of course, I know that the best option is what you have said, and I´ll try it in a future
I think the ac!Material in the query is being recognised as a string instead of a list of string
Please, could you tell me if there is a possibility to fix this?
Well I have no insights into your error message, but if Material is potentially multiple, then the SQL statement would need to utilize the "IN" operator instead of "=". I'm unclear whether any additional syntax adjustments would be needed to accomplish that as well.
I solved the error message by not declaring the variable as "multiple" (it is a list of string).
But the fact is that the query is not updating my database although it has no errors and the process model runs ok...
In this pic, you can see the value that ac!Material adquires (list of string):
And this is how I configured my ac!Material:
My query is:
Please, anyone knows what is happening here? Why isnt the filter by a list of string working?
I would be so grateful with any help to fix it
Thanks a lot