Query Database Service returns Ljava.lang.Long; cannot be cast to java.lang.Long

Hello,
I am attempting to execute the following query using a Query Database Service:

Select item1, item2
from table1
where id = ac!ID
and otherID = ac!otherID

On the Data tab, I have inputs of ac!ID and ac!otherID which are of type Number (Integer) which are set as multiple. I suspect this is what is giving me the issue. I have tried using "IN" within the SQL query but receive an error message upon configuring the SQL. The error message I receive is: "Ljava.lang.Long; cannot be cast to java.lang.Long". The backend is SQLServer. Can you help provide some guidance as to what I am missing? Thanks in advance!

OriginalPostID-238976

  Discussion posts and replies are publicly visible

Parents
  • You won't be able to use multiple values directly with the query DB node, but there is a work-around. I would have to dig up the MSSQL syntax, but for Oracle we can use the INSTR function, this might give you some ideas. In your where clause:

    WHERE INSTR(ac!ID, ',' || id || ',') > 0

    Then your ac!ID input is set as below, single text value:

    ="," & joinarray(pv!id, ",") & ","
Reply
  • You won't be able to use multiple values directly with the query DB node, but there is a work-around. I would have to dig up the MSSQL syntax, but for Oracle we can use the INSTR function, this might give you some ideas. In your where clause:

    WHERE INSTR(ac!ID, ',' || id || ',') > 0

    Then your ac!ID input is set as below, single text value:

    ="," & joinarray(pv!id, ",") & ","
Children
No Data