Skip to main content
C
carlosp5114
September 5, 2023
Solved

SQL clause using WHERE with list of string

  • September 5, 2023
  • 5 replies
  • 0 views

Hi! I´m trying to do the following query with the "query database" smart service:

UPDATE CPF_LINEAS_PEDIDO
SET 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!

    Best answer by mathieud0001

    SQL should look like this:

    SELECT item FROM TABLE WHERE field IN ('item1', 'item2', 'item2')

    To generate the items:

    joinarray(
  a!forEach(
    items: { "item 1", "item 2", "item 3" },
    expression: concat("'", fv!item, "'")
  ),
  ","
)

    5 replies

    mikes0011
    mikes0011
    Brainy
    September 5, 2023

    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.

    C
    carlosp5114Author
    September 5, 2023

    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? 

    mikes0011
    mikes0011
    Brainy
    September 5, 2023

    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.

    mathieud0001
    mathieud0001Answer
    September 7, 2023

    SQL should look like this:

    SELECT item FROM TABLE WHERE field IN ('item1', 'item2', 'item2')

    To generate the items:

    joinarray(
  a!forEach(
    items: { "item 1", "item 2", "item 3" },
    expression: concat("'", fv!item, "'")
  ),
  ","
)

      Terms & ConditionsAccessibility statement