ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Handling the “ORDER BY” clause in an interface

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

image

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

image

Solution 2

Step 1.  Create an Option

image

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

image

Sample Output

image.

Good to see you around…

Keep visiting us at www.odiexperts.com

6 Comments

  1. Can we perform ORDER BY for target flat file.

  2. I’m new to ODI. Can you please specify where to write this code.

  3. 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?

  4. Thanks a lot man 🙂

  5. 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

Leave a Reply

Required fields are marked *.