Skip to main content
jessicaw323350
jessicaw323350
October 3, 2024
Question

Using IN operator on Query Database node

  • October 3, 2024
  • 9 replies
  • 0 views

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?

    9 replies

    A
    aryan.7325
    October 4, 2024

    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, ",")

    jessicaw323350
    jessicaw323350Author
    October 9, 2024

    Hi, I tried that, it didn't work

    somasundaram.d
    somasundaram.d
    October 4, 2024

    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

    mikes0011
    mikes0011
    Brainy
    October 4, 2024

    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...

      mathieud0001
      mathieud0001
      October 5, 2024

      I have the same question. You should use the Query DB node only in rare and exceptional circumstances.

      karumurua531442
      karumurua531442
      October 6, 2024

      hi [mention:1381f4348b27412a952d0c66d18c88be:e9ed411860ed4f2ba0265705b8793d05]  agree to [mention:b45a4f6a20b14a008e4e0ec732a8bf98:e9ed411860ed4f2ba0265705b8793d05] , Could you create a stored procedure in db and execute stored procedure smart service in this case?

      Terms & ConditionsAccessibility statement