Hello Everyone…
We always get a lot of emails asking about how to “Order By” in ODI because of that, here there is a simple way to do it…
Stays as a suggestion to Oracle implement as default feature in forward versions!
A simple example how to handle Order By in ODI.
Solution 1
Step 1. Create an Option
Step 2. Add the code into KM
<% if (odiRef.getOption("ORDER_BY").equals("1")) { %>
ORDER BY
<%=odiRef.getColList("", "[COL_NAME]", ",nt", "", "(UD1)")%>
<%=odiRef.getColList(",", "[COL_NAME]", ",nt", "", "(UD2)")%>
<%=odiRef.getColList(",", "[COL_NAME]", ",nt", "", "(UD3)")%>
<%=odiRef.getColList(",", "[COL_NAME]", ",nt", "", "(UD4)")%>
<%=odiRef.getColList(",", "[COL_NAME]", ",nt", "", "(UD5)")%>
<%=odiRef.getColList(",", "[COL_NAME]", ",nt", "", "(UD6)")%>
<%=odiRef.getColList("", "[COL_NAME]", ",nt", "", "(UD7)")%>
<%=odiRef.getColList("", "[COL_NAME]", ",nt", "", "(UD8)")%>
<%=odiRef.getColList("", "[COL_NAME]", ",nt", "", "(UD9)")%>
<%=odiRef.getColList("", "[COL_NAME]", ",nt", "", "(UD10)")%>
<%} %>
This way you can have the KM to handle with and without Order By option .
Step 3. Mark the Columns accordingly with UD1
Solution 2
Step 1. Create an Option
Step 2 . Add the option into KM
<%=odiRef.getOption("ORDER_BY")%>
Step 3. Add the Order By Clause accordingly
Provide the Order By clause accordingly for N number of columns in the interface
Sample Output
.
Good to see you around…
Keep visiting us at www.odiexperts.com
June 18, 2015 at 6:37 AM
Can we perform ORDER BY for target flat file.
March 11, 2015 at 3:12 PM
I’m new to ODI. Can you please specify where to write this code.
June 17, 2014 at 10:27 PM
This works well when the order by field is one of the fields in the target, but what if you want to export 5 fields into a file, but sort them by a different field in the source?
March 18, 2014 at 6:58 AM
Thanks a lot man 🙂
October 10, 2013 at 11:53 AM
It is a very helpfull example but i need a help form you.
I am not able to find the navigation and steps to create the order by.
Thanks
October 10, 2013 at 4:51 PM
Go the respective step in Km and edit it .