sql - having case block in where clause -

in procedure trying incorporate case block perform conditional check in clause. please here.

below procedure body working fine..

from  table_a  name = p_name , address = p_address , state = p_state , ((p_county null , default_zone = 'y') or (county = p_county)) ; 

now split last segment. county column, standard way, below block work , must have in code(this logic)

where name = p_name , address = p_address , state = p_state , county = p_county 

now suppose, county got during procedure call not available, in case db not return data,, "no_data_found" error come. if error come, have execute below block..

where  name = p_name , address = p_address , state = p_state , default_zone = 'y' 

simplest way, should use procedure twice , make call of second block on exception may procedure bulky , not also..is there way can written in simpler way..

i trying use case block have code not working me.. pls advice

not sure why you'd want rewrite case, 1 solution be:

where   case          when p_county null , default_zone = 'y' county         else p_county         end = county 

i think original or query clearer.

---

in response comment: use dense_rank split table in default , non-default parts. order default_zone, give non-default priority, n comes before y in alphabet.

select  *    (         select  dense_rank() on (                     order default_zone) rn         ,       name         ,       state         ,       county         ,       default_zone            yourtable           county = p_county                 or default_zone = 'y'         ) subqueryalias   rn = 1 -- pick matching county, or else default 

example @ sql fiddle.