SQL LOGIC -

S567

Hi Expert's,

Please help me on below..

Exsisting SQL in DB is below for identifying the customer's

select case when nvl(p.fuel,'*') in ('Y','1') then 2 else 1 end t_type

,case when nvl(p.fuel,'*') in ('Y','1') then  8

      WHEN (t.amount < 0 OR trim(upper(t.t_type_details)) = 'R') THEN 2

      WHEN t.amount > 0    THEN 1 END      t_s_type       

,CASE when nvl(p.fuel,'*') in ('Y','1') then  NULL

      WHEN t.amount < 0 OR trim(upper(t.t_type_details)) = 'R' THEN 2

      WHEN t.amount > 0                             THEN 1 END      s_c       

,case when nvl(p.fuel,'*') in ('Y','1') then  'S' else null end itemtype

,abs(t.amount) amount

from cust t

left join phone_tb p on (t.col1 =p.col1); -- this is giving me 670000 records

No the requirement is to avoid customers where amount > 0 and matches to the phone_tb and fuel = ‘Y’ or ‘1’.

When i added filter for above requirement  i got 1 record where fuel = ‘Y’ or ‘1’ and amount > 0 which i need to avoid,Can someone help how to apply using NOT EXISTS to above SQL

select case when nvl(p.fuel,'*') in ('Y','1') then 2 else 1 end t_type

,case when nvl(p.fuel,'*') in ('Y','1') then  8

      WHEN (t.amount < 0 OR trim(upper(t.t_type_details)) = 'R') THEN 2

      WHEN t.amount > 0    THEN 1 END      t_s_type       

,CASE when nvl(p.fuel,'*') in ('Y','1') then  NULL

      WHEN t.amount < 0 OR trim(upper(t.t_type_details)) = 'R' THEN 2

      WHEN t.amount > 0                             THEN 1 END      s_c       

,case when nvl(p.fuel,'*') in ('Y','1') then  'S' else null end itemtype

,abs(t.amount) amount

from cust t

left join phone_tb p on (t.col1 =p.col1)   where  nvl(p.fuel,'*') in ('Y','1') and amount >0 ;  -- Gave me one record.

Thanks,

Shiva