Hi,
I am using the INSTR parameter in the WHERE clause of the Query DB node. The code works fin in Dev, however, it's throwing the below error in Test.
"There was a problem executing the SQL query. SQL Message : ORA-01460: unimplemented or unreasonable conversion requested"
Below is the code that I am using.
"SELECT CLAIM_REFNUM AS CLAIM_REFNUM, max(PAYMENT_CLAIM_NUMBER) AS PAYMENT_CLAIM_NUMBER, max(PAYMENT_SCHEDULE_NUMBER) AS PAYMENT_SCHEDULE_NUMBER FROM <table>WHERE INSTR(',' || ac!paymentClaimRefNum || ',',',' || CLAIM_REFNUM || ',')>0 GROUP BY CLAIM_REFNUM"
ac!paymentClaimRefNum is a text variable defined as joinarray(pv!paymentClaimSummary_cdt.PAYMENT_CLAIM_ID,",")
pv!paymentClaimSummary_cdt.PAYMENT_CLAIM_ID is an array of string values. It returns 110 rows in Dev and 196 rows in Test.
I wonder if there is a limit on the input values passed in the IN parameter.
Any help would be appreciated
Thanks
Discussion posts and replies are publicly visible
Why the use of INSTR here? Wouldn't WHERE CLAIM_REFNUM IN (....) produce the same results and be more optimal?
I would imagine the length limit on parameter 1 of INSTR would be 4000 chars as it's likely bound as a VARCHAR2.
Hi Tim, I have tried that but unfortunately, the IN parameter doesn't work in query DB smart service, hence INSTR. Check thishttps://community.appian.com/discussions/f/integrations/6403/i-am-passing-an-activity-class-to-a-query-database-smart-service-and-am-receivin/25011#25011
Hi Tim,
Actually ankitab0001 using query db node for fetching data, In this case we have to use INSTR command instead of IN .IN is not working in appian.
Regards,
Bhanu.