By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with test_1(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 t1.no_contract, t1.no_assesment, t1.flag,
case when t1_1.no_contract is null then t1.value_number
else t1_1.value_number
end final_value_number
from test_1 t1
left join test_1 t1_1
on t1.no_contract = t1_1.no_contract
and t1_1.flag = 'N'
| NO_CONTRACT | NO_ASSESMENT | FLAG | FINAL_VALUE_NUMBER |
|---|---|---|---|
| 1 | 24 | N | 200 |
| 1 | 23 | Y | 200 |
| 2 | 24 | null | 200 |
| 2 | 23 | Y | 100 |