3 Replies Latest reply: Feb 27, 2014 12:41 AM by Tina.Wang-Oracle RSS

    Populate row with data from Previous row

    Balaji.tk

      Hi ,

      I have a table A with data as shown below,

       

      Table A

       

      SeqnoCustomerIdDateTier
      110011-Jan-13Silver
      210011-Jan-14Gold
      310011-Jan-15Titatnium
      110055-Jan-13Gold
      210055-Jan-14Titatnium
      310055-Jan-14Platinum

       

      I wants to populate the above data to Table B as shown below using ODI

       

      Table B

       

      SeqnoCustomerIdDateTierFrom_TierTO_Tier
      110011-Jan-13SilverSilverSilver
      210011-Jan-14GoldSilverGold
      310011-Jan-15TitatniumGoldTitatnium
      110055-Jan-13GoldGoldGold
      210055-Jan-14TitatniumGoldTitatnium
      310055-Jan-15PlatinumTitatniumPlatinum

       

      What logic i needs to write in the From_Tier and To_Tier columns in ODI mapping?

       

      Thanks in advance

      Balaji TK

        • 1. Re: Populate row with data from Previous row
          FPonte

          Hi.

           

          Use a Lookup table or a Nested Query in your From_Tier attribute with something like this.

           

           

          (select Tier as From_Tier

          from TableA A2 where A2.CustomerId = A1.CustomerId

          and A2.Date < A1.Date)

           

          A1 and A2 is table A used two time in your mapping.

           

          This select may help you.

          Select

          A1.Seqno

          ,A1.CustomerId

          ,A1.Date

          ,A1.Tier

             nvl((select Tier as From_Tier

             from TableA A2 where A2.CustomerId = A1.CustomerId

             and A2.Date < A1.Date), A1.Tier) From_Tier

          A1.Tier as To_Tier

          From TableA A1

           

          Cheer.

          • 2. Re: Populate row with data from Previous row
            Tina.Wang-Oracle

            INSERT

              /*+  APPEND    PARALLEL  */

              INTO HR.CUSTS_1

              (

                C1 ,

                C2 ,

                C3 ,

                C4 ,

                C5 ,

                C6

              )

            SELECT

              INLINE_VIEW_1.C1 ,

              INLINE_VIEW_1.C2 ,

              INLINE_VIEW_1.C3 ,

              INLINE_VIEW_1.C4 ,

              nvl(CUSTS3.C4,INLINE_VIEW_1.C4) ,

              INLINE_VIEW_1.C4 

            FROM

              HR.CUSTS CUSTS3 ,  (

            SELECT

              INLINE_VIEW.C1  C1 ,

              INLINE_VIEW.C2  C2 ,

              INLINE_VIEW.C1_1  C1_1 ,

              CUSTS2.C1  C1_2 ,

              CUSTS2.C2  C2_1 ,

              CUSTS2.C3  C3 ,

              CUSTS2.C4  C4 ,

              INLINE_VIEW.C1  C1_3 ,

              INLINE_VIEW.C2  C2_2 ,

              INLINE_VIEW.C1_1  C1_4 ,

              CUSTS2.C3  C3_1 ,

              CUSTS2.C4  C4_1  

            FROM

              (

            SELECT

              CUSTS.C1  C1 ,

              CUSTS.C2  C2 ,

              max(CUSTS1.C1)  C1_1  

            FROM

              HR.CUSTS CUSTS ,  HR.CUSTS CUSTS1   

            WHERE

              (CUSTS.C2 = CUSTS1.C2    and CUSTS1.C1 <CUSTS.C1

            )

            GROUP BY

              CUSTS.C1,CUSTS.C2

              ) INLINE_VIEW ,  HR.CUSTS CUSTS2   

            WHERE

              (INLINE_VIEW.C1 = CUSTS2.C1  and INLINE_VIEW.C2=CUSTS2.C2

            )

              ) INLINE_VIEW_1   

            WHERE

              (CUSTS3.C1   = INLINE_VIEW_1.C1_1 and CUSTS3.C2 =INLINE_VIEW_1.C2

            • 3. Re: Populate row with data from Previous row
              Tina.Wang-Oracle

              custs(a)

                              join(a.c2=b.c2 and a.c1>b.c1,make it left outer join)  ->expression(a.c1 as ac1, a.c2 as ac2, b.c1 as bc1)->aggression(max (bc1) group by ac1, ac2)

              custs(b)

                                                                                                                                                                                                                           join (c.c1=aggr. ac1 and c.c2=aggr.ac2) ->expr (c.c1, c.c2,c.c3, c.c4, aggr.bc1)

                                                                                                                                                                                                           custs(c)

                                                                                                                                                                                                                                                                                                                                          join

                                                                                                                                                                                                                                                                                                                           custs (d)

               

               

               

               

              join ( cc2=d.c2 and bc1=d.c1, left outer join)   -> target (for the from_tier, its expression is as below nvl(d.c4, c.c4)