ORA-00939: too many arguments for function error when used substr in SQL Query in DataModel

Shashidhar Goud · July 27

Hi,

I have created a DataModel using below query :

select
HZP.PARTY_NAME,
HZP.PARTY_NUMBER
from
HZ_PARTIES HZP
where
1=1
AND HZP.PARTY_NAME IN (substr(:P_STRING1,:P_START,:P_END))

Here P_STRING1 declared as text parameter with "Text field contains comma-separated values" option enabled and P_START,P_END are Integer type parameters.

But got below error when testing this DataModel :
ORA-00939: too many arguments for function error

Please help.

Thanks
Shashidhar

Nathan CCC · July 27

:P_STRING1 is a comma separated list of values 'A','B','C'. You can not substring it. You can only put 1 string into the first parameter of the substring function but you are putting many strings into argument 1 hence the error.

Here is your user guide for the function SUBSTR

Oracle® Database
SQL Language Reference 23ai F47038-15 July 2024

https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/SUBSTR.html#GUID-C8A20B57-C647-4649-A379-8651AA97187E

argument 1char can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB.

Shashidhar Goud · July 27

@Nathan CCC - Thank you so much for the response.

But if we don't select "Text field contains comma-separated values" the IN condition is not working, it's only accepting 1 string not multiple strings.
Please help, how to make IN working to accept multiple values.

Here I wanted to give below value to parameter and capture PARTY_NAME and PARTY_NUMBER values dynamically :
PARTY_NAME,Test1235,Test2372,,PARTY_NUMBER,10014,1234,,

I have tried REGEXP_SUBSTR as well like below :

HZP.PARTY_NAME IN (REGEXP_SUBSTR(:P_REGEXP,'PARTY_NAME,([^,]+(,[^,]+)*)',1,1,NULL,1)) OR HZP.PARTY_NUMBER IN (REGEXP_SUBSTR(:P_REGEXP,'PARTY_NUMBER,([^,]+(,[^,]+)*)',1,1,NULL,1))

But it's not working got the above mentioned errors.

Please help.

Thanks

Nathan CCC · July 29

Hi,

I have NOT suggested that you change "Text field contains comma-separated values" = False. Your IN string can accept multiple values right now. That is not the issue here. If p null then get all values else get values in p.

… and (coalesce(null,:p) is null or HZP.PARTY_NAME in (:p))

I was simply stating that you cannot pass a comma separated string to function substring as argument 1. Maybe a solution is to substring the list of values on your parameter before you use it in your where clause.

I think the problem is your design. Make the list of values in your parameter a list of party names.

select distinct t.PARTY_NAME as s from HZ_PARTIES t order by 1 asc nulls last

To be honest it makes no sense to me why you are doing a substring. What is string1? What column does it come from? What values does it have? How to you get the list of values? Does it have values like PARTY_NAME,A,B,PARTY_NUMBER,9,9 How does it contains "party name" strings? Why is the position of party name in string 1 variable? If you substring string1 it from X for N characters why will it match party name? Why do you need a variable p_start? Is it that the start position is always the length of constant string "PARTY_NAME" at the start of the string 10? But this is not variable? If not, why does the party name in string 1 sometime start from different character positions? The third argument is the length of substring? Not sure why you called it p_end? How can you ever know the length of the party names inside string1? Why will they have the same number of characters? Is this the INSTR position of hard coded constant string "PARTY_NUMBER" less the start position? So you want to get "A,B" out of "PARTY_NAME,A,B,PARTY_NUMBER,9,9"?

Shashidhar Goud · July 29

Hi @Nathan CCC ,
Thank you so much for the response.

As per my design, I wanted to pass below string as parameter :

PARTY_NAME,Test1235,Test2372,,PARTY_NUMBER,10014,1234,,

so here, I will pass multiple party names and party numbers in 1 parameter.

,, is the delimiter here that separates party names and party number values.

and SQL query will have different conditions in where clause like below :



select HZP.PARTY_NAME, HZP.PARTY_NUMBER from HZ_PARTIES HZP where 1=1


AND HZP.PARTY_NAME IN NVL((REGEXP_SUBSTR(:P_HZ_PARTIES,'PARTY_NAME,([^,]+(,[^,]+))',1,1,NULL,1)),HZP.PARTY_NAME)


AND HZP.PARTY_NUMBER IN NVL((REGEXP_SUBSTR(:P_HZ_PARTIES,'PARTY_NUMBER,([^,]+(,[^,]+))',1,1,NULL,1)),HZP.PARTY_NUMBER)

As mentioned above, will use REGEXP_SUBSTR to strip out the actual party name values and party number values and send them as comma-separated values to IN condition to get all matching records with passed party names and party numbers. But it's not working due to above mentioned error.

Please help.

Thanks

Nathan CCC · July 29

Hi,

OK. So why not change your design. Make it so you have 2 parameters with 2 list of values

p_party_name "Text field contains comma-separated values" = True

select distinct t.PARTY_NAME as s from HZ_PARTIES t order by 1 asc nulls last

p_party_number "Text field contains comma-separated values" = True

select distinct t.PARTY_NUMBER as s from HZ_PARTIES t order by 1 asc nulls last

in your where clause

… 

and (coalesce(null,:p_party_name) is null or HZP.PARTY_NAME in (:p_party_name))
and (coalesce(null,:p_party_number) is null or HZP.PARTY_NUMBER in (:p_party_number))

Sorted?

Shashidhar Goud · July 29

@Nathan CCC - Thanks for quick response.

As per my design, I can't use 2 parameters (due other reasons).
And also can't use like below :

select distinct t.PARTY_NAME as s from HZ_PARTIES t order by 1 asc nulls last

Because party names and party numbers are dynamically passed while running the BI Report.

So how can the above requirement be achieved. Please help.

Thanks

Nathan CCC · July 29

Maybe create an additional parameter p2 with menu lov based on value of p_string1 (sounds like you do not need parameter p_start p_end) to get the list of values you need out of string 1. Default to pass a string of all values rather than null for all values. Then use :p2 in where. Something like lov for :p2 ???

select all substr(:p_string1,10,instr(:p_string1,'PARTY_NUMBER')-10) as s from dual

Oracle Transactional Business Intelligence

Categories

ORA-00939: too many arguments for function error when used substr in SQL Query in DataModel

Best Answer

Answers