Skip to Main Content

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Unable to use analytical functions like lag/lead in odi 12c components except in expression

VSAIPRASADFeb 16 2016 — edited Feb 18 2016

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

;

This post has been answered by JeromeFr on Feb 18 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 17 2016
Added on Feb 16 2016
3 comments
1,422 views