If no value is passed in Multi Select Parameter, instead of showing no result the query is returning all rows of table

0

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 tab
WHERE 
$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:
SELECT
CASE WHEN
$X{IN, 0, PARAM_MULTI_PROJ_ID}
  THEN 0
  ELSE 1
  END as filter_check
FROM DUAL

 

Thanks

inform2jayant's picture
Joined: May 19 2017 - 1:43am
Last seen: 1 month 6 days ago

2 Answers:

-1

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

mlopez_1's picture
493
Joined: Oct 21 2013 - 8:08am
Last seen: 6 hours 39 min ago
0

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?

 

Thanks

Jayant

 

inform2jayant's picture
Joined: May 19 2017 - 1:43am
Last seen: 1 month 6 days ago
Feedback
randomness