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