Sorting in ODI 11G

Sorting in ODI 11G

1. It is equal to ORDER BY clause in SQL
2. it is available as a component in ODI 12 C

STEP 1 : Creating and Reverse Engineering Source

1. We can use EMP table of SCOTT schema as Source

STEP 2 : Creating and Reverse Engineering Target

1. Duplicate EMP data store and rename as TGT_EMP_SORT
2. Drag and drop into Target Model

STEP 3 : Change KM

1. Duplicate the existing KM IKM SQL Control Append
2. Rename as IKM SQL CONTROL APPEND SORT
3. Open this KM → Select Details → Double Click on Insert New Rows → end of the code add below code

<%=odiRef.getOption("ORDER_BY")%>

4. Right Click on IKM SQL CONTROL APPEND → Select New Option

5. Provide name and type  as shown → save and close the KM

STEP 4 : Creating Interface/Mapping

1. Create interface with the name of m_SORT_IN_ODI11G
2. Click on mapping tab → Drag and drop source EMP  and target  TGT_EMP_SORT in respective locations

3. Click on Flow tab
4. Select Target and select IKM SQL Control Append Sort → Change options
1. ORDER_BY : ORDER BY DEPTNO ,SAL
2. FLOW_CONTROL : False
3. Truncate :True
4. Create Target Table :True

5. Click on SAVE
6. Click on RUN → Observe Code

7. Observe Target Table data