Skip to Main Content
  • Questions
  • Only to get rows from the table where null value in the columns are less than 3

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Yashasvi .

Asked: November 19, 2020 - 5:31 am UTC

Answered by: Chris Saxon - Last updated: November 19, 2020 - 10:30 am UTC

Category: SQL - Version: Oracle 11g

You Asked

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

and we said...

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

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.