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

# Populate row with data from Previous row

Hi ,

I have a table A with data as shown below,

Table A

 Seqno CustomerId Date Tier 1 1001 1-Jan-13 Silver 2 1001 1-Jan-14 Gold 3 1001 1-Jan-15 Titatnium 1 1005 5-Jan-13 Gold 2 1005 5-Jan-14 Titatnium 3 1005 5-Jan-14 Platinum

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

Table B

 Seqno CustomerId Date Tier From_Tier TO_Tier 1 1001 1-Jan-13 Silver Silver Silver 2 1001 1-Jan-14 Gold Silver Gold 3 1001 1-Jan-15 Titatnium Gold Titatnium 1 1005 5-Jan-13 Gold Gold Gold 2 1005 5-Jan-14 Titatnium Gold Titatnium 3 1005 5-Jan-15 Platinum Titatnium Platinum

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

Balaji TK

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

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.

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

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

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)