12 Replies Latest reply on Nov 4, 2010 11:01 PM by Aketi Jyuuzou

    Need help on the query to remove Null records

    448749
      Hi,
      I have a table with the following data.

      COL1 COL2 COL3
      1     a1     NULL     NULL
      2     b1     NULL     x3
      3     c1     NULL     y3
      4     NULL     d2     z3
      5     NULL     a2     NULL
      6     NULL     c2     NULL
      7     NULL     b2     NULL


      I want to get output like this: (i.e. All null values have to be eliminated from the all the columns).


      COL1 COL2 COL3
      1     a1     d2     x3
      2     b1     a2     y3
      3     c1     c2     z3
      4     NULL     b2     NULL

      Is there any way to get it achieved thru a query?

      Thanks,
      GV

      Edited by: GV2 on Apr 13, 2009 8:03 AM
        • 1. Re: Need help on the query to remove Null records
          Avinash Tripathi
          Hi,

          What logic did you applied to remove the NULL values manually??


          Thanks,
          Avinash
          • 2. Re: Need help on the query to remove Null records
            448749
            No, I am not sure how to do it.
            • 3. Re: Need help on the query to remove Null records
              690792
              Try this:
              Select * from (Select COL1, rownum rown from TAB where COL1 is not null ) a,
              (Select COL2 , rownum rown from TAB where COL2 is not null) b,
              (Select COL3 , rownum rown from TAB where COL3 is not null) c where a.rown=b.rown and a.rown=c.rown;

              Edited by: JustasVred on Apr 13, 2009 8:46 AM
              • 4. Re: Need help on the query to remove Null records
                448749
                I tried this query. Its working, but it is eliminated the fourth row in the table.

                COL1 COL2 COL3
                1     b1     a2     y3
                2     c1     c2     z3
                3     a1     d2     x3

                There is a fouth record for the column COL2.


                -GV
                • 5. Re: Need help on the query to remove Null records
                  Frank Kulash
                  Hi,
                  GV2 wrote:
                  Hi,
                  I have a table with the following data.

                  COL1 COL2 COL3
                  1     a1     NULL     NULL
                  2     b1     NULL     x3
                  3     c1     NULL     y3
                  4     NULL     d2     z3
                  5     NULL     a2     NULL
                  6     NULL     c2     NULL
                  7     NULL     b2     NULL
                  It would be better if you posted CREATE TABLE and INSERT statements. It looks like you have 4 columns, but you only give 3 column names. I'll assume the table is called table_x, and the first column (the numbers 1-7, which is imoportant because it determines the output order) is called id.
                  I want to get output like this: (i.e. All null values have to be eliminated from the all the columns).

                  COL1 COL2 COL3
                  1     a1     d2     x3
                  2     b1     a2     y3
                  3     c1     c2     z3
                  4     NULL     b2     NULL

                  Is there any way to get it achieved thru a query?
                  It would help if you formatted the output and explained it. Type these 6 characters
                  {code}
                  (small letters only, inside curly brackets) before and after formatted text, to preserve spacing.
                  For example:

                  "The output I want is
                  {code}
                  R COL1 COL2 COL3
                  1     a1     d2     x3
                  2     b1     a2     y3
                  3     c1     c2     z3
                  4     NULL     b2     NULL
                  {code}
                  The column r does not exist in my table; it has to be created by the query.
                  The values on the row with r=1 are the non-NULL values with the lowest id in table_x.
                  The values on the row with r=2 are the non-NULL values with the 2nd lowest id in table_x.
                  The values on the row with r=N are the non-NULL values with the N-th lowest id in table_x.
                  Every row of output muust have at least one non-NULL values in col1, col2 or col3."

                  If that's what you want, you can get it using the analytic ROW_NUMBER fucntion to generate the r column. This will be easiest if you un-pivot the data so that col1, col2 and col3 are all in the same column (on 3 different rows), then use a WEHERE clause to eliminate the NULLs, and finally pivot the results back into 4 columns, like this:
                  WITH     cntr         AS
                  (
                       SELECT     LEVEL     AS c
                       FROM     dual
                       CONNECT BY     LEVEL     <= 3
                  ),     unpivoted   AS
                  (
                       SELECT     id
                       ,     c
                       ,     CASE     c
                                 WHEN  1  THEN  col1
                                 WHEN  2  THEN  col2
                                 WHEN  3  THEN  col3
                            END     AS txt
                       FROM          table_x
                       CROSS JOIN     cntr
                  )
                  ,     got_r     AS
                  (
                       SELECT     unpivoted.*
                       ,     ROW_NUMBER () OVER ( PARTITION BY c
                                                 ORDER BY         id
                                         )  AS r
                       FROM     unpivoted
                       WHERE     txt     IS NOT NULL
                  )
                  SELECT     r
                  ,     MAX (CASE WHEN c = 1 THEN txt END)     AS col1
                  ,     MAX (CASE WHEN c = 2 THEN txt END)     AS col2
                  ,     MAX (CASE WHEN c = 3 THEN txt END)     AS col3
                  FROM     got_r
                  GROUP BY     r
                  ORDER BY     r;
                  This assumes that col1, col2 and col3 all have the same data type. If not, explicitly convert them to the same type (probably VARCHAR2) in the CASE statement in the subquery I called unpivoted.
                  • 6. Re: Need help on the query to remove Null records
                    666352
                    Hi,

                    COL1 COL2 COL3
                    1 a1 NULL NULL
                    2 b1 NULL x3
                    3 c1 NULL y3
                    4 NULL d2 z3
                    5 NULL a2 NULL
                    6 NULL c2 NULL
                    7 NULL b2 NULL

                    If i well understand

                    merge line 1 and line4
                    merge line2 and line5
                    merge line3 and line6
                    line 7

                    Ithink your desired output is :

                    a1 d2 z3
                    b1 a2 x3
                    c1 c2 y3
                    * b2 *

                    With this method you scan your table one time.
                    select  col1,col2,col3
                    from
                    (
                    select col1,col2,col3,found
                     from t
                    model
                    return updated rows
                    dimension by ( row_number()over(partition by 1 order by rownum) rn )
                    measures(  count(1) over(partition by 1 ) cpt,rownum rr,col1,col2,col3, 0 found ,0 fixed, case when col1 is not null and col2 is null  and col3 is null then 1
                                 when col1 is  null and col2 is not null  and col3 is null then 2
                                 when col1 is  null and col2 is  null  and col3 is not null then 3
                                                                                    when col1 is not  null and col2 is  null  and col3 is not null then 13
                                  when col1 is not  null and col2 is not  null  and col3 is  null then 12
                                  when col1 is  null and col2 is not  null  and col3 is not  null then 23
                    end situation  ) ignore nav
                     rules iterate(1000) until (iteration_number + 1 =cpt[1])
                    (fixed[1] =  case when situation[iteration_number+1]=1 then   min( case when  situation=23 and found=0  then  rr end )[any]
                                                when situation[iteration_number+1]=2 then   min( case when  situation=13 and found=0 then  rr end )[any]
                               when situation[iteration_number+1]=3 then   min( case when  situation=12 and found=0 then  rr end )[any]
                               when situation[iteration_number+1]=13 then   min( case when  situation=2 and found=0 then  rr end )[any]
                                                 when situation[iteration_number+1]=12 then   min( case when  situation=3 and found=0 then  rr end )[any]
                               when situation[iteration_number+1]=23 then   min( case when  situation=1  and found=0 then  rr end )[any] end,
                    col1[iteration_number+1] = case when situation[iteration_number+1]=2  then col1[fixed[1] ]
                                                                    when situation[iteration_number+1]=3 then col1[fixed[1] ]
                                   when situation[iteration_number+1]=23  then col1[fixed[1] ]
                                  else  col1[iteration_number+1]   end ,
                    col2[iteration_number+1] = case when situation[iteration_number+1]=1  then col2[fixed[1] ]
                                                                    when situation[iteration_number+1]=13  then col2[fixed[1] ]
                                   when situation[iteration_number+1]=3  then col2[fixed[1] ]
                                  else  col2[iteration_number+1]   end ,
                    col3[iteration_number+1] order by rn  = case when situation[iteration_number+1]=1 and fixed[1]  is present  then col3[fixed[1] ]
                                                                   when situation[iteration_number+1]=2 and fixed[1]  is present  then col3[fixed[1] ]
                               when situation[iteration_number+1]=12 and fixed[1]  is present  then col3[fixed[1] ]
                       else  col3[iteration_number+1] end,
                    found[fixed[1] ]=1,
                    situation[iteration_number+1] = case when fixed[1] is not null then null else situation[iteration_number+1]  end ))
                    where found=0
                    /
                    SQL> with t as (   
                      2  select 'a1' COL1, NULL COL2, NULL COL3 from dual union all
                      3  select  'b1' COL1, NULL COL2, 'x3' COL3 from dual union all
                      4  select  'c1' ,NULL ,'y3' from dual union all
                      5  select NULL, 'd2', 'z3' from dual union all
                      6  select  NULL,'a2', NULL from dual union all
                      7  select NULL ,'c2' ,NULL from dual union all
                      8  select  NULL,'b2', NULL from dual )
                      9  select  col1,col2,col3
                     10  from 
                     11  (
                     12  select col1,col2,col3,found
                     13   from t
                     14  model 
                     15  return updated rows
                     16  dimension by ( row_number()over(partition by 1 order by rownum) rn )
                     17  measures(  count(1) over(partition by 1 ) cpt,rownum rr,col1,col2,col3, 0 found ,0 fixed, case 
                    when col1 is not null and col2 is null  and col3 is null then 1 
                     18               when col1 is  null and col2 is not null  and col3 is null then 2
                     19               when col1 is  null and col2 is  null  and col3 is not null then 3
                     20                                                                  when col1 is not  null and col2
                     is  null  and col3 is not null then 13
                     21                when col1 is not  null and col2 is not  null  and col3 is  null then 12 
                     22                when col1 is  null and col2 is not  null  and col3 is not  null then 23
                     23  end situation  ) ignore nav
                     24   rules iterate(1000) until (iteration_number + 1 =cpt[1])
                     25  (fixed[1] =  case when situation[iteration_number+1]=1 then   min( case when  situation=23 and 
                    found=0  then  rr end )[any]
                     26                              when situation[iteration_number+1]=2 then   min( case when  situati
                    on=13 and found=0 then  rr end )[any]
                     27             when situation[iteration_number+1]=3 then   min( case when  situation=12 and found=0
                     then  rr end )[any] 
                     28             when situation[iteration_number+1]=13 then   min( case when  situation=2 and found=0
                     then  rr end )[any]  
                     29                               when situation[iteration_number+1]=12 then   min( case when  situa
                    tion=3 and found=0 then  rr end )[any] 
                     30             when situation[iteration_number+1]=23 then   min( case when  situation=1  and found=
                    0 then  rr end )[any] end,
                     31  col1[iteration_number+1] = case when situation[iteration_number+1]=2  then col1[fixed[1] ]  
                     32                                                  when situation[iteration_number+1]=3 then col1[
                    fixed[1] ]
                     33                 when situation[iteration_number+1]=23  then col1[fixed[1] ] 
                     34                else  col1[iteration_number+1]   end ,
                     35  col2[iteration_number+1] = case when situation[iteration_number+1]=1  then col2[fixed[1] ]  
                     36                                                  when situation[iteration_number+1]=13  then col
                    2[fixed[1] ]
                     37                 when situation[iteration_number+1]=3  then col2[fixed[1] ] 
                     38                else  col2[iteration_number+1]   end ,
                     39  col3[iteration_number+1] order by rn  = case when situation[iteration_number+1]=1 and fixed[1] 
                     is present  then col3[fixed[1] ]  
                     40                                                 when situation[iteration_number+1]=2 and fixed[1
                    ]  is present  then col3[fixed[1] ]
                     41             when situation[iteration_number+1]=12 and fixed[1]  is present  then col3[fixed[1] ]
                    
                     42     else  col3[iteration_number+1] end,
                     43  found[fixed[1] ]=1,
                     44  situation[iteration_number+1] = case when fixed[1] is not null then null else situation[iterati
                    on_number+1]  end ))
                     45  where found=0
                     46  /
                    
                    CO CO CO
                    -- -- --
                    a1 d2 z3
                    b1 a2 x3
                    c1 c2 y3
                       b2
                    
                    SQL> 
                    Regards salim.
                    • 7. Re: Need help on the query to remove Null records
                      690792
                      Ok. Lets use outer join.

                      SQL> Select COL1, COL2, COL3 from (Select rownum rown from TAB where COL1 is not null
                      2 UNION
                      3 Select rownum rown from TAB where COL2 is not null
                      4 UNION
                      5 Select rownum rown from TAB where COL3 is not null) main,
                      6 (Select COL1, rownum rown from TAB where COL1 is not null ) a,
                      7 (Select COL2 , rownum rown from TAB where COL2 is not null) b,
                      8 (Select COL3 , rownum rown from TAB where COL3 is not null) c where main.rown=a.rown(+) and main.rown=b.rown(+) and main.rown=c.rown(+);

                      COL1 COL2 COL3
                      1 a1 d2 x3
                      2 b1 a2 y3
                      3 c1 c2 z3
                      4 NULL b2 NULL


                      P.S. Do not forget to give points for helpful answers:-)
                      • 8. Re: Need help on the query to remove Null records
                        MichaelS
                        Or
                        SQL>  with t as (   
                          select 'a1' col1, null col2, null col3 from dual union all
                          select 'b1' col1, null col2, 'x3' col3 from dual union all
                          select 'c1',  null, 'y3' from dual union all
                          select null, 'd2',  'z3' from dual union all
                          select null, 'a2',  null from dual union all
                          select null ,'c2',  null from dual union all
                          select null, 'b2',  null from dual 
                        )
                        --
                        --
                        select col1, col2, col3 from 
                        (select rownum r1, col1 from t order by 1 nulls last) t1
                         full outer join 
                        (select rownum r2, col2 from (select col2 from t order by 1 nulls last)) t2
                         on t1.r1 = t2.r2
                         full outer join
                        (select rownum r3, col3 from (select col3 from t order by 1 nulls last)) t3
                         on t2.r2 = t3.r3
                        where coalesce(col1,col2,col3) is not null
                        
                        CO CO CO
                        -- -- --
                        a1 a2 x3
                        b1 b2 y3
                        c1 c2 z3
                           d2   
                        
                        4 rows selected.
                        • 9. Re: Need help on the query to remove Null records
                          74666
                          It is simple.

                          Select replace(cola, null, 'a',cola) from your table_name;

                          Edited by: sk123 on Apr 14, 2009 3:53 AM
                          • 10. Re: Need help on the query to remove Null records
                            Aketi Jyuuzou
                            I made 3 solutions :-)
                            create table modet as
                            select 'a1' COL1, NULL COL2, NULL COL3 from dual union all
                            select 'b1',NULL,'x3' from dual union all
                            select 'c1',NULL,'y3' from dual union all
                            select NULL,'d2','z3' from dual union all
                            select NULL,'a2',NULL from dual union all
                            select NULL,'c2',NULL from dual union all
                            select NULL,'b2',NULL from dual;
                            This solution used model caluse.
                            Hahaha this method scans table ones only B-)
                            select COL1,COL2,COL3
                            from (select COL1,COL2,COL3
                                    from modeT
                                   model
                                   dimension by(Row_Number() over(order by COL1,COL2,COL3) as rn,
                                                nvl2(COL2,Row_Number() over(order by COL2),0) as rn2,
                                                nvl2(COL3,Row_Number() over(order by COL3),0) as rn3)
                                   measures(COL1,COL2,COL3)
                                  rules(
                                  COL2[any,any,any] order by rn= max(COL2)[any,CV(rn),any],
                                  COL3[any,any,any] order by rn= max(COL3)[any,any,CV(rn)]))
                             where coalesce(COL1,COL2,COL3) is not null
                            order by COL1,COL2,COL3;
                            
                            COL1  COL2  COL3
                            ----  ----  ----
                            a1    a2    x3
                            b1    b2    y3
                            c1    c2    z3
                            null  d2    null
                            • 11. Re: Need help on the query to remove Null records
                              Aketi Jyuuzou
                              This solution used OLAP :8}
                              We may use Lag and Lead instead of Olap which use range between
                              select COL1,Col2,Col3
                              from (select COL1,
                                    case when rn <= Col2Rn
                                         then max(Col2) over(order by Col2Rn
                                                             range between abs(Col2Rn-rn) preceding
                                                                       and abs(Col2Rn-rn) preceding)
                                         else max(Col2) over(order by Col2Rn
                                                             range between abs(Col2Rn-rn) following
                                                                       and abs(Col2Rn-rn) following) end as Col2,
                                    case when rn <= Col3Rn
                                         then max(Col3) over(order by Col3Rn
                                                             range between abs(Col3Rn-rn) preceding
                                                                       and abs(Col3Rn-rn) preceding)
                                         else max(Col3) over(order by Col3Rn
                                                             range between abs(Col3Rn-rn) following
                                                                       and abs(Col3Rn-rn) following) end as Col3
                                    from (select COL1,COL2,COL3,
                                          Row_Number() over(order by COL1,COL2,COL3) as rn,
                                          Row_Number() over(order by COL2,COL1,COL3) as Col2Rn,
                                          Row_Number() over(order by COL3,COL1,COL2) as Col3Rn
                                            from modet))
                               where coalesce(COL1,Col2,Col3) is not null
                              order by COL1,Col2,Col3;
                              • 12. Re: Need help on the query to remove Null records
                                Aketi Jyuuzou
                                This solution used REFERENCE model ;-)
                                Today,I studied REFERENCE model
                                select *
                                  from modet model
                                  REFERENCE Col1model on
                                  (select Col1,Row_Number() over(order by Col1) as rn1 from modet where Col1 is not null)
                                    dimension by(rn1)
                                    measures(Col1)
                                  REFERENCE Col2model on
                                  (select Col2,Row_Number() over(order by Col2) as rn2 from modet where Col2 is not null)
                                    dimension by(rn2)
                                    measures(Col2)
                                  REFERENCE Col3model on
                                  (select Col3,Row_Number() over(order by Col3) as rn3 from modet where Col3 is not null)
                                    dimension by(rn3)
                                    measures(Col3)
                                  MAIN MAINmodel
                                  dimension by(Row_Number() over(order by COL1,Col2,Col3) as rn)
                                  measures(COL1,COL2,COL3)
                                  rules(
                                  Col1[any] = Col1model.Col1[cv(rn)],
                                  Col2[any] = Col2model.Col2[cv(rn)],
                                  Col3[any] = Col3model.Col3[cv(rn)])
                                order by rn;
                                **********************************************************
                                My homepage
                                http://www.geocities.jp/oraclesqlpuzzle/
                                My openSourceSoft OTN-thread viewer
                                [Off topic] I made OTN-thread viewer