Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test12 (customer_type, currency, balance_no) as
select 1, 'AZN', 10001 from dual
union all
select 1, 'USD', 10002 from dual
union all
select 1, 'ALL', 10003 from dual
union all
select 2, 'AZN', 10004 from dual
union all
select 2, 'USD', 10005 from dual
union all
select 2, 'ALL', 10006 from dual
6 rows affected

-- original code wth a2='USD', which matches both
select t.customer_type, t.balance_no
from test12 t
where t.customer_type=1 and t.currency= 
(case when 'USD' in t.currency then 'USD'
 else 'ALL' 
 end);


CUSTOMER_TYPE
BALANCE_NO


1
10002

1
10003


-- original code wth a2='XYZ', which matches only 'ALL'
select t.customer_type, t.balance_no
from test12 t
where t.customer_type=1 and t.currency= 
(case when 'XYZ' in t.currency then 'XYZ'
 else 'ALL' 
 end);


CUSTOMER_TYPE
BALANCE_NO


1
10003


select t.customer_type, t.balance_no
from test12 t
where t.customer_type = 1
and t.currency in ('USD', 'ALL')
order by case when t.currency = 'USD' then 1 else 2 end
fetch first 1 row only;


CUSTOMER_TYPE
BALANCE_NO


1
10002


select t.customer_type, t.balance_no
from test12 t
where t.customer_type = 1
and t.currency in ('XYZ', 'ALL')
order by case when t.currency = 'XYZ' then 1 else 2 end
fetch first 1 row only;


CUSTOMER_TYPE
BALANCE_NO


1
10003


select t.customer_type, t.balance_no
from test12 t
where t.customer_type = 1
and (
  t.currency = 'USD'
  or
  (
    t.currency = 'ALL'
    and not exists (
      select null
      from test12
      where customer_type = 1
      and currency = 'USD'
    )
  )
)


CUSTOMER_TYPE
BALANCE_NO


1
10002


select t.customer_type, t.balance_no
from test12 t
where t.customer_type = 1
and (
  t.currency = 'XYZ'
  or
  (
    t.currency = 'ALL'
    and not exists (
      select null
      from test12
      where customer_type = 1
      and currency = 'XYZ'
    )
  )
)


CUSTOMER_TYPE
BALANCE_NO


1
10003


select t.customer_type, t.balance_no
from test12 t
where t.customer_type = 1
and t.currency = 
 case
   when 'USD' in (select currency from test12 where customer_type = 1)
   then 'USD'
   else 'ALL'
 end;


CUSTOMER_TYPE
BALANCE_NO


1
10002


select t.customer_type, t.balance_no
from test12 t
where t.customer_type = 1
and t.currency = 
 case
   when 'XYZ' in (select currency from test12 where customer_type = 1)
   then 'XYZ'
   else 'ALL'
 end;


CUSTOMER_TYPE
BALANCE_NO


1
10003