I have a case to read specific flags ('Y', 'N', NULL), like the condition below there are 2 conditions.

1. if the detail no_contract has flag 'N' then Final_value_number follows value_number flag 'N'
2. if the detail no_contract does not have flag 'N' then Final_value_number follows value_number for each flag itself

CodePudding user response：

I understand your "if the detail no_contract" phrase you need to decide based on partition of rows with same no_contract. Hence use window function. For partitions without flag='N' the case expression defaults to null and coalesce applies then.

I assume the value_number is never null and also the min function is chosen randomly since you haven't specified the behaviour for two flag='N' rows within same no_contract.

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

Db fiddle

CodePudding user response：

I'd suggest you to self-join table if possible. My approach outer joins you table with itself filtered for 'N' columns. This might be quicker than using analytical functions or a subuery. Check the performance

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'

db fiddle