Conditional WHERE clause with CASE statement in Oracle

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) ;

2 Answers 2

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)); 

where (:stateCode = '') or vw.state_cd in (:stateCode)