I need to build a query in Oracle SQL such that it should fetch records with only null ornnn_id

From this data:

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
3574953     1       08-AUG-2003 10.20.10
3574953     1       08-AUG-2003 10.20.10
3574953     1       08-AUG-2003 10.20.10
3574953     1       08-AUG-2003 10.20.10
3574953     1       08-AUG-2003 10.20.10
12345      null     null
12345      null     null
1212       3        08-AUG-2003 10.20.10
1212       3        08-AUG-2003 10.20.10

I need to query such that only records with null ind_id and effect_from_dat are fetched eg the row with ornnn_id 12345

My query

select ornnn_id, ind_id,sl_effect_from_dat from table where sl_ind is null and sl_effect_from_dat is null

is fetching ornnn_id 3574953 and 12345 – but I only want 12345.

>Solution :

There are a few ways to do this, depending on what output you want; here are a couple:

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

That just gets the unique IDs – the other columns seems pointless as you know they are null; but if you want them you can change the first line to:

select ornnn_id, null as ind_id, null as effect_from_dat

or

select ornnn_id, max(ind_id) as ind_id, max(effect_from_dat) as effect_from_dat

Or this gets all the rows if you don’t just want the unique IDs:

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)
)

fiddle

There are others, like MT0’s analytic count. It depends what you actually want, and how the various approaches perform with your data.