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

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..."

CodePudding user response：

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;