I'm brand-new to the Oracle world so this could be a softball. In working with an SSRS report, I'm passing in a string of states to a view. The twist is that the users could also pick a selection from the state list called "[ No Selection ]" ... (that part was not by doing and I'm stuck with implementing things this way)
If they choose the No Selection option, then I just want to return all states by default, otherwise return just the list of states that are in my comma-separated list.
This really seems like it should be easy but I'm stuck. Here is the code I have so far (just trying to get a sample to work) but my eyes have finally gone crossed trying to get it going.
Could anybody give me some direction on this one please?
begin :stateCode:='MO,FL,TX'; --:stateCode:='[ No Selection ]'; end; / select count(*) as StateCount, :stateCode as SelectedVal from hcp_state vw where (case when (:stateCode = '') then (1) when (:stateCode != '') then (vw.state_cd in (:stateCode)) (else 0) end) ;
You can write the where
clause as:
where (case when (:stateCode = '') then (1)
when (:stateCode != '') and (vw.state_cd in (:stateCode)) then 1
else 0)
end) = 1;
Alternatively, remove the case
entirely:
where (:stateCode = '') or
((:stateCode != '') and vw.state_cd in (:stateCode));
Or, even better:
where (:stateCode = '') or vw.state_cd in (:stateCode)