This is the table that I have

This is how I think I would code it

select
case when cnt_dealno = '1' then amount
else amount = 0
end as new_amount
from tab

What I try to do is when cnt_dealno = 1, then the amount is not changing. But when the cnt_dealno is not equal to 1, then the amount will become 0.

Is there any way to code this in SQL?

CodePudding user response：

Should be

SQL> with test (cnt_dealno, dealno, amount) as
  2    (select 1, 'ABC', 3000 from dual union all
  3     select 2, 'ABC', 3000 from dual union all
  4     select 3, 'ABC', 3000 from dual union all
  5     select 1, 'DEF', 5000 from dual union all
  6     select 2, 'DEF', 5000 from dual
  7    )
  8  select cnt_dealno, dealno, amount,
  9    --
 10    case when cnt_dealno = 1 then amount
 11         else 0
 12    end as new_amount
 13  from test;

CNT_DEALNO DEA     AMOUNT NEW_AMOUNT
---------- --- ---------- ----------
         1 ABC       3000       3000
         2 ABC       3000          0
         3 ABC       3000          0
         1 DEF       5000       5000
         2 DEF       5000          0

SQL>

Why do you think you need PL/SQL?