Hi Tom,
Thanks for your support!
Imagine a table having n number of columns and let say having 100 rows in the table. So I only want to fetch those rows out of 100 where null values in the n columns are less than 3.
Col1 Col2 Col3 Col4 Col5 Col6 Col7 Col8 Col9 Col10
123 null 234 456 null null null 122 05895 null
123 456 789 NULL 987 333 111 3333 NULL 876
49944 03039 O987 INC 0393 NMJ 93938 KFF 93I9 KNDJ
Consider above table so as per requirement only row no 2 and 3 will come in output as in row 2 there are only 2 nulls i.e less than 3 and row 3 has no nulls.
Please help.
I would appreciate hearing from You
Regards,
Yashasvi
You can use NVL2 to map:
- non-nulls to 0
- nulls to 1
For each column. Then sum up the totals for the columns, returning those where the total < 3:
with rws as (
select level c1,
case mod ( level, 2 )
when 0 then null
else level
end c2 ,
case mod ( level, 3 )
when 0 then null
else level
end c3,
case mod ( level, 5 )
when 1 then null
else level
end c4
from dual
connect by level <= 6
)
select * from rws;
C1 C2 C3 C4
1 1 1 <null>
2 <null> 2 2
3 3 <null> 3
4 <null> 4 4
5 5 5 5
6 <null> <null> <null>
with rws as (
select level c1,
case mod ( level, 2 )
when 0 then null
else level
end c2 ,
case mod ( level, 3 )
when 0 then null
else level
end c3,
case mod ( level, 5 )
when 1 then null
else level
end c4
from dual
connect by level <= 6
)
select * from rws
where nvl2 ( c2, 0, 1 ) +
nvl2 ( c3, 0, 1 ) +
nvl2 ( c4, 0, 1 ) < 3 ;
C1 C2 C3 C4
1 1 1 <null>
2 <null> 2 2
3 3 <null> 3
4 <null> 4 4
5 5 5 5