oracle - How to create a select statement that satisifies multiple condition from a single column? -

here have 3 columns : id(number),part_name(varchar) , part_value(clob)(all 3 forms primary key). i'm having scenerio in such way should check multiple conditions same column. eg : particular id, should check different part_name , corresponding part_value. below wrong know. reference i'm including

select count(0)  my_table   id = 'xyz' , (         (             (                 part_type='sw nut prod'                  , dbms_lob.compare(part_value,'nut , non-standard key')=0             )          or               (                 part_type='sw nut prod'                  , dbms_lob.compare(part_value,'limes key , document')=0             )          or (                 part_type='sw nut prod'                  , dbms_lob.compare(part_value,'limes physical key , document')=0             )         )          , (             part_type='trim'              , dbms_lob.compare(part_value,'false')=0         )  ) 

we have acheive this. have tried using self joints. didn't helped because have big queries wher supposed check 10 part_name @ time.any suggesstion me

these query may help. adds column cnt each row in table, informs if conditions id satisfied:

with data (     select id, part_type, part_value,         case when part_type='sw nut prod'          , (dbms_lob.compare(part_value,'nut , non-standard key')=0            or dbms_lob.compare(part_value,'limes key , document')=0            or dbms_lob.compare(part_value,'limes physical key , document')=0)          1 end c1,       case when part_type='trim' , dbms_lob.compare(part_value,'false')=0          1 end c2     my_table) select id, part_type, part_value, case when c1 > 0 , c2 > 0 1 else 0 end cnt     (     select id, part_type, part_value,         count(c1) on (partition id) c1, count(c2) on (partition id) c2        data) 

^sqlfiddle

for reason don't want group by, of course can simplify output using clause. if interested in particular id add where id=xyz in first subquery. in sqlfiddle added second id, not conditions have been met.