Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table your_table (ornnn_id, ind_id, effect_from_dat) as
select 3574953, null, null from dual union all
select 3574953, null, null from dual union all
select 3574953, null, null from dual union all
select 3574953, null, null from dual union all
select 3574953, null, null from dual union all
select 3574953, null, null from dual union all
select 3574953, null, null from dual union all
select 3574953, 1, to_date ('08-AUG-2003 10.20.10', 'DD-MON-YYYY HH24.MI.SS') from dual union all
select 3574953, 1, to_date ('08-AUG-2003 10.20.10', 'DD-MON-YYYY HH24.MI.SS') from dual union all
select 3574953, 1, to_date ('08-AUG-2003 10.20.10', 'DD-MON-YYYY HH24.MI.SS') from dual union all
select 3574953, 1, to_date ('08-AUG-2003 10.20.10', 'DD-MON-YYYY HH24.MI.SS') from dual union all
select 3574953, 1, to_date ('08-AUG-2003 10.20.10', 'DD-MON-YYYY HH24.MI.SS') from dual union all
select 12345, null, null from dual union all
select 12345, null, null from dual union all
select 1212, 3, to_date ('08-AUG-2003 10.20.10', 'DD-MON-YYYY HH24.MI.SS') from dual union all
select 1212, 3, to_date ('08-AUG-2003 10.20.10', 'DD-MON-YYYY HH24.MI.SS') from dual
16 rows affected

select ornnn_id, ind_id, effect_from_dat
from your_table
where ind_id is null
and effect_from_dat is null


ORNNN_ID
IND_ID
EFFECT_FROM_DAT


3574953
null
null

3574953
null
null

3574953
null
null

3574953
null
null

3574953
null
null

3574953
null
null

3574953
null
null

12345
null
null

12345
null
null


select ornnn_id
from your_table
group by ornnn_id
having max(ind_id) is null
and max(effect_from_dat) is null


ORNNN_ID


12345


select ornnn_id, null as ind_id, null as effect_from_dat
from your_table
group by ornnn_id
having max(ind_id) is null
and max(effect_from_dat) is null


ORNNN_ID
IND_ID
EFFECT_FROM_DAT


12345
null
null


select ornnn_id, max(ind_id) as ind_id, max(effect_from_dat) as effect_from_dat
from your_table
group by ornnn_id
having max(ind_id) is null
and max(effect_from_dat) is null


ORNNN_ID
IND_ID
EFFECT_FROM_DAT


12345
null
null


select ornnn_id, ind_id, effect_from_dat
from your_table t1
where ind_id is null
and effect_from_dat is null
and not exists (
  select null
  from your_table t2
  where t2.ornnn_id = t1.ornnn_id
  and (t2.ind_id is not null or t2.effect_from_dat is not null)
)


ORNNN_ID
IND_ID
EFFECT_FROM_DAT


12345
null
null

12345
null
null

ORNNN_ID	IND_ID	EFFECT_FROM_DAT
3574953	null	null
3574953	null	null
3574953	null	null
3574953	null	null
3574953	null	null
3574953	null	null
3574953	null	null
12345	null	null
12345	null	null