SQL If "x" includes all possible values
I am writing a query in Oracle SQL and I am trying to add a prompt that limits the results when validated, and if left unchecked, the criteria are ignored (or return all possible results). Specifically, the query returns all employees around the world. The checkbox (possible Y or N values) is to restrict the results to North America only (using the Country field and the US and CAN values). There are a number of other countries, and instead of listing them all, I would just like to say "include all values" or "ignore cutoffs".
Essentially what I'm trying to do is: If the checkbox is checked, only return employees whose value in the country field is US or CAN. If the box is unchecked, return all employees regardless of the value in the country field.
I have tried case statement
[COUNTRY] = CASE WHEN (PROMPT = 'Y') THEN ('USA' OR 'CAN')
WHEN (PROMPT = 'N') THEN (LIKE '%') END
but I am getting error messages for 1) multiple values after THEN (using "US" or "CAN") and 2) how to encode "any value" in the PROMPT = N section.
I've tried many permutations from OR, IN, ANY for multiple THEN values to no avail. And I've researched and tried the% and _ wildcard permutations for the "any value" section. I also tried to write this as an IF statement, but that didn't work either.
I am new to sql and any help is greatly appreciated! Thank!
source to share
The easiest way to do this would be with the OR instruction. No further testing is needed when the prompt value indicates "give me everything".
where (
(:PROMPT = 'Y' and COUNTRY IN ('USA', 'CAN') )
or (:PROMPT = 'N')
)
Note: The syntax for denoting a parameter will depend on how you invoke the request. I used :PROMPT
above, but changed it to whatever you need.
source to share