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
Just a thought.
Please check if pv!paymentClaimSummary_cdt.PAYMENT_CLAIM_ID have any other special characters apart from below ones.
list of Character Set Support:
% ' ' ( ) * + - , . / \ : ; < > = ! _ & ~ { } | ^ ? $ # @ " [ ]
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.
Not sure what is your use case but looking st the code the first question is: Why you have a activity class parameter in a query??
I am not sure but, If i am reading well the code you are trying to do should be something like this
"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"
Please note this
INSTR(',' || '“ & ac!paymentClaimRefNum &” '|| ',',',' || CLAIM_REFNUM || ','
Instead of
INSTR(',' || ac!paymentClaimRefNum || ',',',' || CLAIM_REFNUM || ','
You mention that variable is multiple values?
How you query looks like with multiple values in a hard coded syntax? I think you need to create a rule to create that piece of code(string), like this
INSTR(',' || '“ & rule!XYZ_getQueryString(ac!paymentClaimRefNum) &” '|| ',',',' || CLAIM_REFNUM || ','
Not sure those queries actually work, that’s why I asked for the hard coded version with multiple values.
Hope this helps
Jose
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 Jose
Thanks for your reply. I don't see any expression editor in the SQL statement section of the Query DB smart service. It accepts the activity class parameter as an input (without the "&") but I am not sure if it will accept any rule. Anyhow, the code works fine in DEV and also in TEST but for a limited number of input characters. Therefore I wondered if there's any limit to how many values we can pass in the "IN" parameter and if there is any limit what would be the work around.
Hi Ricky, There are no special characters apart from _.
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.
I should have asked this before, On which version are you working? Are you on premise?
José
Yes it's on premise. The version is 18.2