I have two inputs. "Sites" & "Controllers". Controllers are the children of Sites.
"Controllers" Custom Input Query
SELECT id, name FROM controllers WHERE $X{IN, site_id, site_id_1}
When site_id_1 has nothing selected
I expect: the return set to be empty
I get: all the controllers in the DB
Question: How do I make the query return no results when site_id_1 has no options selected?
What I've tried:
SELECT id, name FROM controllers WHERE (CASE WHEN ($P{site_id_1} = NULL) THEN id = 0 ELSE $X{IN, site_id, site_id_1} END)
However I don't know the data type of $P{site_id_1}, thus I don't know how to write an expression to test if the parameter is empty.
Thanks in Advance!
1 Answer:
Posted on March 15, 2014 at 5:40pm
Why don't you just check the input parameter? Do something like in the following thread but replace MyVerifiedParam default to something that'll return no result.
http://community.jaspersoft.com/questions/537583/validate-input-controls
I would like to check the parameter, but I'm not sure how. I tried "CASE WHEN ($P{site_id_1} = NULL)", but it always will fail that check.