invalid relational operator error

Hemant_Jain

Hi - I am trying to write a query to pull report parameter value in report layout, however due to system limitation whenever my default selection for any parameter is All then its appearing as blank in report.
To show All as my selected parameter in report output, I created a data base query which is - Select Case when :p_xyz is Null then 'All' else :p_xyz end from dual
This query is working fine when all parameter value is selected for if a single value is selected but not working when multiple parameter value is selected and giving me error invalid relational operator. I tried using In function after else but no luck.
Can someone help, thanks.

Kevin M-Oracle

See Tips for Multi-value Parameters: Performance Best Practices

Rgds,

Kevin

Mallikarjuna Kuppauru-Oracle

Hi @Hemant_Jain

Did you created the dependent prompts means based on one parameter and 2nd will be refresh. Did you checked the execution logs , how the query is generating during online mode.

Regards,

Arjun

Hemant_Jain

Hi @Mallikarjuna Kuppauru-Oracle - I tried creating parameter based on another parameter, but its picking only one value. When i checked the xml file, issue is in case of multiple values selected for a parameter then its populating in a vector and therefore neither nvl nor case function is working.

Below is the sql am currently using to pull parameter result (example for department parameter) in report output and to show 'All' if all is selected - select case when :p_dept is null then 'All' else :p_dept end from dual

This is working when 'All' is selected or a single value but not when multiple values are selected.

Hemant_Jain

Hi @Kevin M-Oracle - I tried it the way explained on the page shared by you, but no luck :-(

Kevin M-Oracle

See: Tips for Multi-value Parameters

Performance Best Practices

Rgds,

Kevin