Forum Stats

  • 3,815,586 Users
  • 2,259,054 Discussions
  • 7,893,176 Comments

Discussions

Reject customers - NOT EXSIST

S567
S567 Member Posts: 426 Red Ribbon
edited Oct 22, 2019 9:57AM in Social Groups

Hi Expert's,

I need a help from team i have existing sql below in my DB

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

Requirement is  to Reject customers where t.amount > 0 and matches to the phone_tb and p.fuel in ( ‘Y’ or ‘1’)

When i checked using above requirement  i got 1 record where fuel = ‘Y’ or ‘1’ and amount > 0 which i need to reject,Can someone help how to aplly 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,

S