sql - Conditional WHERE clause with CASE statement in Oracle -

i'm brand-new oracle world softball. in working ssrs report, i'm passing in string of states view. twist users pick selection state list called "[ no selection ]" ... (that part not doing , i'm stuck implementing things way)

if choose no selection option, want return states default, otherwise return list of states in comma-separated list.

this seems should easy i'm stuck. here code have far (just trying sample work) eyes have gone crossed trying going.

could give me direction on 1 please?

 begin   :statecode:='mo,fl,tx';   --:statecode:='[ no selection ]'; end; /  select count(*) statecount, :statecode selectedval hcp_state vw    (case        when (:statecode = '') (1)       when (:statecode != '') (vw.state_cd in (:statecode))       (else 0)   end) ; 

you can write where clause as:

where (case when (:statecode = '') (1)             when (:statecode != '') , (vw.state_cd in (:statecode)) 1             else 0)        end) = 1; 

alternatively, remove case entirely:

where (:statecode = '') or       ((:statecode != '') , vw.state_cd in (:statecode)); 

or, better:

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