Creating a new column in SQL Oracle with a subquery - any suggestions?
You may use the conditional max window function as the following:
select t.*, 
  case 
    when TYPE = 'BASE' 
       then max(case when TYPE = 'TOTAL' then CLNTAMOUNT end) over
               (partition by YearMonth, Code)
       else 0
    end as clntamount_der
from tbl t
order by 
  YearMonth, Code,
  case TYPE
    when 'BASE' then 1
    when 'ADJUST' then 2
    when 'TOTAL' then 3
  end

demo
Also, it seems that the TOTAL is the sum of other types (BASE, ADJUST), in this case, it would be better to calculate the TOTAL on the fly (and not store it as a separate row) to avoid data inconsistency. i.e. when you update the CLNTAMOUNT value where type = 'BASE' how would you reflect this to the value where type = 'TOTAL'?
consider the following without having the TOTAL type stored in the table.
select d.*
  from
(
  select t.YearMonth, t.Code, t.Type, t.CLNTAMOUNT,
    case 
      when TYPE = 'BASE' 
         then sum(t.CLNTAMOUNT) over (partition by t.YearMonth, t.Code)
         else 0
      end as clntamount_der
  from tbl t
  
  union all
  
  select t.YearMonth, t.Code, 'TOTAL',
          sum(t.CLNTAMOUNT), 0 
  from tbl t
  group by t.YearMonth, t.Code
) d
order by 
  d.YearMonth, d.Code,
  case d.TYPE
    when 'BASE' then 1
    when 'ADJUST' then 2
    when 'TOTAL' then 3
  end

demo

I have the columns Yearmonth, code, type and clntamount in my table. I want to derive column clntamount_der.
Table view

I want the 110 value in clntamount with the type = Total to be in the column clntamount_der where the type = base and the rest two rows(adj and total) as zero.
Please help with the logic.
I tried case statemnt but it didnt work maybe a subquery will work
