CODEWITHSUNDEEP
oracle-databaseoracle-data-integrator
66_66
66_66

Reputation: 35

how to use rownumber over partition in ODI MAPPING

My requirement is to get the recent record grouped by columns c1,c2.I have 50 columns in my source, using query i can apply rownumber() over partition by c1,c2 order by record_time desc, and pick the record where rownumber=1. In short, my oracle query would be:

select c1,c2,....,c50 from (select c1,c2,....,c50, row_number() over (partition by c1,c2 order by record_time desc ) rn from table) where rn = 1;

How can I achieve this using ODI mapping? Please suggest.

Thanks

Upvotes: 0

Views: 1865

Answers (1)

user14317785
user14317785

Reputation: 11

You have not mentioned the version of ODI you are using, assuming you are using ODI 11g. You can create a yellow interface and create a column for rownum with expression as below row_number() over (partition by c1,c2 order by record_time desc)

Now use this yellow interface as source in your interface and apply filter on rownum column as below rownum = 1

While using this you will have to make sure your KM is not adding group by function, else it'll end up in error.

Upvotes: 1

Related Questions