SQL sub-query: select all values but for one type of values only higher than

Question

Is it possible to get all values for all currencies but for one particular currency only those that are higher than let's say 10000 USD?

select case
    when currency_id = '57' then 'EUR'
    when currency_id = '26' then 'USD'
    when currency_id = '51' then 'HKD' end as CCY,
    amount, ECPNBR, value_date
    from money_transfer
    where (select amount from money_transfer where currency_id ='26')>10000 order by amount desc;

this gets error that "subquery returns more than 1 value..."

Answer 1

maybe put your query like below

select 
 case
  when m.currency_id = '57' then 'EUR'
  when m.currency_id = '26' then 'USD'
  when m.currency_id = '51' then 'HKD'
  else '' 
 end,
 m.amount, m.ECPNBR, m.value_date
from money_transfer  where ( m.currency_id='26' and m.amount>10000) OR (m.currency_id<>'26')
order by m.amount desc;