inform2jayant Posted May 19, 2017 Share Posted May 19, 2017 Hi,I'm using Multi-Select Parameter in my report and using this syntax "$X{IN, dummy, PARAM_MULTI_PROJ_ID}" in the query. QUERY 1:Now this works fine if I'm put some value for this parameter and I run the report. But when I do not give any value in this parameter then instead of showing no results it simply selects all the rows of that table and provide the output which is not desired as per my requirement. Below is the query where i'm using this parameter. Please let me know if my implementation is in correct somewhere:SELECT tab.id AS id FROM my_table tabWHERE $X{IN, tab.ID, PARAM_MULTI_PROJ_ID} QUERY 2:Also, Im using a separate queyr where I'm checking whether the Multi-Select parameter is null or not. Please check the below query and let me know if it is correct:SELECTCASE WHEN$X{IN, 0, PARAM_MULTI_PROJ_ID} THEN 0 ELSE 1 END as filter_checkFROM DUAL Thanks Link to comment Share on other sites More sharing options...
mlopez_1 Posted May 19, 2017 Share Posted May 19, 2017 You can put this in your where statement of your query:( $P{PARAM_MULTI_PROJ_ID} = '[]' AND tab.ID = -1 OR $X{IN, tab.ID, PARAM_MULTI_PROJ_ID})Where tab.ID = -1 is a value that doesn't exit.Mariano Link to comment Share on other sites More sharing options...
inform2jayant Posted May 22, 2017 Author Share Posted May 22, 2017 Hi Lopez,The peace of code that you have shared gives an error for this line " $P{PARAM_MULTI_PROJ_ID} = '[]' " as INVALID COLUMN TYPE. Could please suggest some alternate way? ThanksJayant Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now