12 Replies Latest reply on Oct 29, 2010 11:23 PM by Aketi Jyuuzou

    Carry Forward

    546431
      Hi All

      I was just wondering if there is any function out there to carry forward a value onto x number of lines. I am not using version 10 so I am unable to use last_value.

      Basically I have a column such as the one shown below. Where ever there is a null value I need to bring down the last value. Any ideas on how to achieve this?

      ID COL_A COL_B
      1 10 10
      2 NULL 10
      3 30 30
      4 40 40
      5 NULL 40
      6 NULL 40
      7 NULL 40
      8 50 50
      9 NULL 50
      10 60 60

      Any help would be great

      Michelle
        • 1. Re: Carry Forward
          3520
          lines. I am not using version 10 so I am unable to
          use last_value.
          And what version are you using?
          Analytics has been around since 8i (in SQL only however, not PL/SQL) if I remember correctly.

          Gints Plivna
          http://www.gplivna.eu
          • 2. Re: Carry Forward
            Aketi Jyuuzou
            Do you want to use "Ignore nulls" On Oracle 9i ?
            http://www.geocities.jp/oraclesqlpuzzle/8-12.html
            • 3. Re: Carry Forward
              Lukasz Mastalerz
              Try something like this:

              UPDATE t a SET col_a = (
              SELECT MAX(col_a) FROM t b WHERE b.id < a.id
              ) WHERE col_a IS NULL
              • 4. Re: Carry Forward
                546431
                I am using version 9.

                Im not sure if the max function will work this as if I have three rows and the first row is the max row but I want to fill the third row with the second row would it not take the first row as this is the max value
                • 5. Re: Carry Forward
                  508998
                  If you need last value you can use the below query

                  select e.*,rowid
                  from emp e
                  where rowid = (select max(rowid) from emp)

                  But that will bring max value of the table.
                  • 6. Re: Carry Forward
                    Sven W.
                    with t as (select 1 ID, 10 COL_A, 10 COL_B from dual UNION ALL
                    select 2, NULL, 10 from dual UNION ALL
                    select 3, 30, 30 from dual UNION ALL
                    select 4, 40, 40 from dual UNION ALL
                    select 5, NULL, 40 from dual UNION ALL
                    select 6, NULL, 40 from dual UNION ALL
                    select 7, NULL, 40 from dual UNION ALL
                    select 8, 50, 50 from dual UNION ALL
                    select 9, NULL, 50 from dual UNION ALL
                    select 10, 60, 60 from dual)
                    select t1.*, nvl(col_a, (select t3.col_a from t t3 where t3.id = 
                                               (select max(t2.id) from t t2 where t2.id < t1.id and t2.col_a is not null) )
                                     )
                    from t t1
                    This should work in 9i. In 10g there are more possibilities. Especially with the KEEP function.
                    • 7. Re: Carry Forward
                      21205
                      I'm pretty sure that you can use analytic function in Oracle 9
                      select id
                           , col_a
                           , max (col_a) over (order by id)
                        from x
                      ;
                      • 8. Re: Carry Forward
                        bencol
                        But that only works if id and col a are both ordered the same:
                          1  with t as (select 1 ID, 10 COL_A from dual UNION ALL
                          2  select 2, NULL from dual UNION ALL
                          3  select 3, 30 from dual UNION ALL
                          4  select 4, 40 from dual UNION ALL
                          5  select 5, NULL from dual UNION ALL
                          6  select 6, NULL from dual UNION ALL
                          7  select 7, NULL from dual UNION ALL
                          8  select 8, 50 from dual UNION ALL
                          9  select 9, NULL from dual UNION ALL
                        10  select 11, 40 from dual UNION ALL
                        11  select 12, NULL from dual UNION ALL
                        12  select 10, 60 from dual)
                        13  select t1.*, nvl(col_a, (select t3.col_a from t t3 where t3.id =
                        14                             (select max(t2.id) from t t2 where t2.id < t1.id and t2.col_a is not null) )
                        15                   ) Sven
                        16  ,max(col_a) over (order by id) alex
                        17* from t t1
                        dw2@DWDEVB> /

                                ID      COL_A       SVEN       ALEX
                        __________ __________ __________ __________
                                 1         10         10         10
                                 2                    10         10
                                 3         30         30         30
                                 4         40         40         40
                                 5                    40         40
                                 6                    40         40
                                 7                    40         40
                                 8         50         50         50
                                 9                    50         50
                                10         60         60         60
                                11         40         40         60
                                12                    40         60
                        • 9. Re: Carry Forward
                          Rob van Wijk
                          And in addition:
                          SQL> create table mytable
                            2  as
                            3  select 1 id, 10 col_a, 10 col_b from dual union all
                            4  select 2, null, 10 from dual union all
                            5  select 3, 30, 30 from dual union all
                            6  select 4, 70, 70 from dual union all
                            7  select 5, null, 70 from dual union all
                            8  select 6, null, 70 from dual union all
                            9  select 7, null, 70 from dual union all
                          10  select 8, 50, 50 from dual union all
                          11  select 9, null, 50 from dual union all
                          12  select 10, 60, 60 from dual
                          13  /

                          Tabel is aangemaakt.

                          SQL> set autotrace on explain
                          SQL> select t1.*
                            2       , nvl
                            3         ( col_a
                            4         , ( select t3.col_a
                            5               from mytable t3
                            6              where t3.id =
                            7                    ( select max(t2.id)
                            8                        from mytable t2
                            9                       where t2.id < t1.id
                          10                         and t2.col_a is not null
                          11                    )
                          12           )
                          13         ) x
                          14    from mytable t1
                          15  /

                                  ID      COL_A      COL_B          X
                          ---------- ---------- ---------- ----------
                                   1         10         10         10
                                   2                    10         10
                                   3         30         30         30
                                   4         70         70         70
                                   5                    70         70
                                   6                    70         70
                                   7                    70         70
                                   8         50         50         50
                                   9                    50         50
                                  10         60         60         60

                          10 rijen zijn geselecteerd.


                          Uitvoeringspan
                          ----------------------------------------------------------
                             0      SELECT STATEMENT Optimizer=CHOOSE
                             1    0   FILTER
                             2    1     TABLE ACCESS (FULL) OF 'MYTABLE'
                             3    1     SORT (AGGREGATE)
                             4    3       TABLE ACCESS (FULL) OF 'MYTABLE'
                             5    0   TABLE ACCESS (FULL) OF 'MYTABLE'
                          This is Sven's solution. Correct, but using three table scans.

                          SQL> select id
                            2       , col_a
                            3       , max (col_a) over (order by id)
                            4    from mytable
                            5  /

                                  ID      COL_A MAX(COL_A)OVER(ORDERBYID)
                          ---------- ---------- -------------------------
                                   1         10                        10
                                   2                                   10
                                   3         30                        30
                                   4         70                        70
                                   5                                   70
                                   6                                   70
                                   7                                   70
                                   8         50                        70
                                   9                                   70
                                  10         60                        70

                          10 rijen zijn geselecteerd.


                          Uitvoeringspan
                          ----------------------------------------------------------
                             0      SELECT STATEMENT Optimizer=CHOOSE
                             1    0   WINDOW (SORT)
                             2    1     TABLE ACCESS (FULL) OF 'MYTABLE'
                          Alex's solution does not give the last value but the max value.

                          SQL> select id
                            2       , col_a
                            3       , col_b
                            4       , max(decode(id,maxid,col_a)) over(partition by maxid) last_col_a_value
                            5    from ( select id
                            6                , col_a
                            7                , col_b
                            8                , max(nvl2(col_a,id,null)) over (order by id) as maxid
                            9             from mytable
                          10         )
                          11   order by id
                          12  /

                                  ID      COL_A      COL_B LAST_COL_A_VALUE
                          ---------- ---------- ---------- ----------------
                                   1         10         10               10
                                   2                    10               10
                                   3         30         30               30
                                   4         70         70               70
                                   5                    70               70
                                   6                    70               70
                                   7                    70               70
                                   8         50         50               50
                                   9                    50               50
                                  10         60         60               60

                          10 rijen zijn geselecteerd.


                          Uitvoeringspan
                          ----------------------------------------------------------
                             0      SELECT STATEMENT Optimizer=CHOOSE
                             1    0   SORT (ORDER BY)
                             2    1     WINDOW (SORT)
                             3    2       VIEW
                             4    3         WINDOW (SORT)
                             5    4           TABLE ACCESS (FULL) OF 'MYTABLE'
                          This one is using the SQL to which Aketi linked to, and displays the last value using only one table access.

                          Regards,
                          Rob.
                          • 10. Re: Carry Forward
                            21205
                            Yes, you're right... <<need more coffee>>
                            • 11. Re: Carry Forward
                              Sven W.
                              Thanks Rob! I really didn't like my version because of many table scans. Very clever usage of NVL2 function and over clause!
                              • 12. Re: Carry Forward
                                Aketi Jyuuzou
                                Oh.
                                My site is mentioned.
                                Thanks Rob.

                                my site (emulate first_Value with "ignore nulls" on Oracle9i)
                                http://www.geocities.jp/oraclesqlpuzzle/8-10.html
                                my site (emulate Last_Value with "ignore nulls" on Oracle9i)
                                http://www.geocities.jp/oraclesqlpuzzle/8-12.html
                                My site is written by Japanese language.

                                I translated from Japanese to English for above site (http://www.geocities.jp/oraclesqlpuzzle/8-12.html).
                                --before Data--
                                LastValueTable2
                                Code  SortKey   Val
                                ----  -------  ----
                                AAAA      100  null
                                AAAA      200   888
                                AAAA      300   777
                                AAAA      400  null
                                BBBB      500   555
                                BBBB      600  null
                                BBBB      610   333
                                BBBB      620  null
                                BBBB      630   666
                                CCCC      100  null
                                --output Data--
                                Code  SortKey   Val  Last_Value
                                ----  -------  ----  ----------
                                AAAA      100  null        null
                                AAAA      200   888         888
                                AAAA      300   777         777
                                AAAA      400  null         777
                                BBBB      500   555         555
                                BBBB      600  null         555
                                BBBB      610   333         333
                                BBBB      620  null         333
                                BBBB      630   666         666
                                CCCC      100  null        null
                                *****explain*****
                                On Oracle9i,
                                We want to get ResultSet which is derived below query.
                                select Code,SortKey,Val,
                                Last_Value(Val ignore nulls)
                                over(partition by Code order by SortKey) as "Last_Value"
                                from LastValueTable2;
                                "ignore nulls" has been supported since oracle10g.
                                --DDL--
                                create table LastValueTable2 as
                                select 'AAAA' as Code,100 as SortKey,to_number(null) as Val from dual
                                union select 'AAAA',200, 888 from dual
                                union select 'AAAA',300, 777 from dual
                                union select 'AAAA',400,null from dual
                                union select 'BBBB',500, 555 from dual
                                union select 'BBBB',600,null from dual
                                union select 'BBBB',610, 333 from dual
                                union select 'BBBB',620,null from dual
                                union select 'BBBB',630, 666 from dual
                                union select 'CCCC',100,null from dual;
                                --SQL--
                                select code,SortKey,Val,
                                max(decode(SortKey,maxSortKey,Val)) over(partition by Code,maxSortKey) as "Last_Value"
                                from (select Code,SortKey,Val,
                                      max(nvl2(Val,SortKey,null)) over(partition by Code order by SortKey) as maxSortKey
                                      from LastValueTable2)
                                order by code,SortKey;