Hi I am a beginner to the ODI 12C
I am trying to get two latest comments done on product for a given product id. and load them into a target.
I have a source table something like
SR_NO product comments LAST_UPDATED_TS
1 car good 2015/05/15 8:30:25
1 car average 2015/05/15 10:30:25
2 jeep super 2015/05/15 11:30:25
1 car bad 2015/05/15 11:30:25
2 jeep horrible 2015/05/15 9:30:25
2 jeep excellent 2015/05/15 12:30:25
I want a target table based on their last updated time stamp like(Two latest Comments)
SR_NO Comment1 Comment2
1 bad average
2 excellent super
I used the below logic to get the records in SQL developer but in ODI 12C I am not able to achieve this by mapping one source to target table by applying analytical functions to the columns in target table. Can anyone help me to solve this
SELECT * FROM (
SELECT SR_NO, Comment1, LAG(Comment1,1,NULL) OVER ( PARTITION BY SR_NO ORDER BY LAST_UPDATED_TS ASC) Comment2,
ROW_NUMBER() OVER (PARTITION BY SR_NO ORDER BY LAST_UPDATED_TS DESC ) RN
FROM Source_table
) M
WHERE RN =1
;