By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with t(no_contract, no_assesment, flag, value_number ) as (
select 1, 23, 'Y' , 100 from dual union all
select 1, 24, 'N' , 200 from dual union all
select 2, 23, 'Y' , 100 from dual union all
select 2, 24, NULL, 200 from dual
)
select t.*
, coalesce(
min(case when flag = 'N' then value_number end) over (partition by no_contract),
value_number
) as Final_value_number
from t
| NO_CONTRACT | NO_ASSESMENT | FLAG | VALUE_NUMBER | FINAL_VALUE_NUMBER |
|---|---|---|---|---|
| 1 | 23 | Y | 100 | 200 |
| 1 | 24 | N | 200 | 200 |
| 2 | 23 | Y | 100 | 100 |
| 2 | 24 | null | 200 | 200 |