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

Question

Here I have 3 columns : ID(NUMBER),PART_NAME(VARCHAR) and PART_VALUE(CLOB)(All three forms a Primary Key). I'm having a scenerio in such a way that i should check multiple conditions for the same column. For eg : For Particular ID, I Should check Different Part_Name and its corresponding part_value. The below is wrong i know. Just for reference i'm including

SELECT COUNT(0) 
FROM MY_TABLE  
WHERE ID = 'XYZ'
AND (
        (
            (
                PART_TYPE='SW NUT Prod' 
                AND DBMS_LOB.COMPARE(PART_VALUE,'NUT and Non-Standard Key')=0
            ) 
        OR  
            (
                PART_TYPE='SW NUT Prod' 
                AND DBMS_LOB.COMPARE(PART_VALUE,'LIMES Key and Document')=0
            ) 
        OR (
                PART_TYPE='SW NUT Prod' 
                AND DBMS_LOB.COMPARE(PART_VALUE,'LIMES Physical Key and Document')=0
            )
        ) 
        AND (
            PART_TYPE='TRIM' 
            AND DBMS_LOB.COMPARE(PART_VALUE,'FALSE')=0
        ) 
)

We have to acheive this. I have tried using self joints. But that didn't helped because we have very big queries wher we are supposed to check for 10 Part_name at a time.Any suggesstion would help me

Answer 1

These query may help. It adds column cnt to each row in table, which informs if all conditions for this id were satisfied:

with data as (  
  SELECT id, part_type, part_value,  
      case when PART_TYPE='SW NUT Prod' 
        and (DBMS_LOB.COMPARE(PART_VALUE,'NUT and Non-Standard Key')=0 
          or DBMS_LOB.COMPARE(PART_VALUE,'LIMES Key and Document')=0 
          or DBMS_LOB.COMPARE(PART_VALUE,'LIMES Physical Key and Document')=0) 
        then 1 end c1,
      case when PART_TYPE='TRIM' AND DBMS_LOB.COMPARE(PART_VALUE,'FALSE')=0 
        then 1 end c2
    FROM MY_TABLE)
select id, part_type, part_value, case when c1 > 0 and c2 > 0 then 1 else 0 end cnt  
  from (
    select id, part_type, part_value,
        count(c1) over (partition by id) c1, count(c2) over (partition by id) c2 
      from data)

^SQLFiddle

For some reason you don't want group by, but of course you can simplify output using this clause. If you are interested only in particular id add where id=XYZ in first subquery. In SQLFiddle I added second ID, where not all conditions have been met.