ora-00939 error in reporting services, SSRS

Question

Hi,

I have an SSRS report , Oracle is my backend and am using this following query for dataset of my second parameter.

select distinct X from v_stf_sec_user_staffing_center usc where usc.center_group_id in ( select distinct center_group_id from V_T_STAFFING_CENTER_GROUP scg where INSTR(','||REPLACE(:PI_REGION_LIST,' ')||',', ','||scg.group_abbreviation||',') > 0) and usc.nt_user_name=:PI_NT_USER_NAME

Here PI_REGION_LIST is a multivalued parameter of string type. and PI_NT_USER_NAME is a default string valued parameter

this query works fine when i try to execute in manulally in the Data tab , also in the Oracle tool. But when i run the report in SSRS and select more than 3 values for the parameter PI_REGION_LIST the report throws an error on this dataset, ora-00939 error,too many arguments for function. I am not able to figure out the error here.

Please help me with an idea.

Thanks in advance, Suni.

Answer 1

The REPLACE function takes three string parameters, the last being optional.

The MS documentation says a multi-valued parameter has the following restriction "The query must use an IN clause to specify the parameter."

If you don't actually need to do the REPLACE to get rid of spaces, you should be able to do something like

WHERE scg.group_abbreviation in (:PI_REGION_LIST)