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

johng373 over 8 years ago

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!

Discussion posts and replies are publicly visible

0 sanjays0006 over 8 years ago

John,
Even though you have set ID and otherID as multiple in Appian, SQL is treating it as a string itself.
You have to find a way to split the values before the query.
I have not done it through the SQL statement. But we can do it through Query Rule, Query Entity or executing stored procedure.
Cancel
Vote Up -1 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Chris over 8 years ago

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, ",") & ","
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel