Objective is to fix the problem when selecting multiple values from a parameter then SQL throws exception: ORA-00909: invalid number of arguments
Create data set and pass the parameter as shown
In general BI Publisher supports all, single, multiple values to be passed as filters to the Report. But to handle show multiple (all) values including null this functionality
can be achieved using the condition as shown.
select STATE_CODE,
STATE_NAME
from
WC_STATE_D
where
COUNTRY='US'
and (STATE_CODE IN (:states) or 'All' IN (:states ||
'All'))
order by
STATE_NAME ASC
Create a parameter and select list of values defined for this parameter
A menu type parameter "states" presents state LOVs to the user from which to select a value or values to pass to the data source. Define the list of values as "States List" using SQL query type.
 |
| Parameter - menu type |
Note: Choose any parameter option NULL Value Passed or All Values Passed.To allow multiple entries from the list, selected "Multiple Selection" option.
If the parameter values are not dependent on any other parameters, uncheck 'Refresh other parameters on change" option.
Click View Data and select All from the list menu.
The following figure shows how the states menu type parameter will
display list of state(s) in the report when multiple selection is enabled:
 |
| All states |
 |
| Selected state |
I hope the above solution is helpful to avoid the sql error ORA-00909: invalid number of arguments
Awesome!! thanks it worked
ReplyDelete... IN (:xdo_user_roles)
ReplyDeletedoes not work :(
For me it works for only one parameter, if add another Menu Parameter it does not work.
ReplyDeleteI am using between clause for parameter and it is giving same error
ReplyDeleteAND hp.party_name between nvl(:p_CUST_low,hp.party_name) and nvl(:p_CUST_high,hp.party_name)
ReplyDeleteOur List of values is based on SQL query SQL query has null value and other distinct values as Output.
ReplyDeleteBI report works fine for ALL condition where it implies to NULL value + other distinct values
But when specific value is selected then null value is also applied along with it. so BI report filters null value and specific value records. Which is wrong?
Can you please suggest how can null value be not selected when specific value is selected in multiselect prompt? Please help.
It Worked
ReplyDeleteIt worked like wonder for me for 4 filters
ReplyDeleteI have used same condition which is mentioned in this mail , but still I am getting error ORA-00909: invalid number of arguments.
ReplyDeleteUAT is going on , please can you help me someone for fix this issue.
AND (org_def.organization_code IN (:p_orgcode) or 'All' IN (:p_orgcode || 'All'))
AND org_def.organization_code IN ( Decode(:p_orgcode,NULL,org_def.organization_code,:p_orgcode ) )
what is the difference between null value passed and all values passed under can select all
ReplyDeleteWHERE (TRIM(TYPE_CD) IN (:P_SUSP_TYPE) or 'All' IN (:P_SUSP_TYPE || 'All'))
ReplyDeletefor me working,thank you!
Great and that i have a nifty provide: How To House Renovation zombie house renovation
ReplyDelete