ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

KM FOR UNION BASED ON IKM SQL CNTRL APPEND

This KM is do UNION in ODI  which created based on IKM SQL Control Append and support Flow control too and is based on the full outer join concept

clip_image002

Map the column in this format

NVL( <SOURCE1.COLUMN1 , SOURCE2.COLUMN1>
NVL(<SOURCE1.COLUMN2,SOURCE2.COLUMN2>
and so on..

and do a simple join between the two source tables  and select this KM and execute the interface.

clip_image003

The codes are shown below.

INSERT FLOW INTO I$ TABLE

insert into <%=snpRef.getTable("L","INT_NAME","A")%>
(
<%=snpRef.getColList("", "[COL_NAME]", ",nt", "", "((INS and !TRG) and REW)")%>
)
select <%=snpRef.getPop("DISTINCT_ROWS")%>
<%=snpRef.getColList("", "[EXPRESSION]", ",nt", "", "((INS and !TRG) and REW)")%>
from <%=odiRef.getSrcTablesList("", "[SCHEMA].[TABLE_NAME]", " FULL OUTER JOIN ", "")%>
on <%=odiRef.getJoinList("","([EXPRESSION])"," and ","")%>
where <% if (snpRef.getPop("HAS_JRN").equals("0")) { %>
(1=1)
<%} else {%>
JRN_FLAG <> 'D'
<% } %>
<%=snpRef.getFilter()%>
<%=snpRef.getJrnFilter()%>
<%=snpRef.getGrpBy()%>
<%=snpRef.getHaving()%>

INSERT INTO NEW ROWS

<%if ( snpRef.getUserExit("FLOW_CONTROL").equals("1") ) { %>
insert into <%=snpRef.getTable("L","TARG_NAME","A")%>
(
<%=snpRef.getColList("", "[COL_NAME]", ",nt", "", "((INS and !TRG) and REW)")%>
<%=snpRef.getColList(",", "[COL_NAME]", ",nt", "", "((INS and TRG) and REW)")%>
)
select <%=snpRef.getColList("", "[COL_NAME]", ",nt", "", "((INS and !TRG) and REW)")%>
<%=snpRef.getColList(",", "[EXPRESSION]", ",nt", "", "((INS and TRG) and REW)")%>
from <%=snpRef.getTable("L","INT_NAME","A")%>
<% }
else { %>
insert into <%=snpRef.getTable("L","TARG_NAME","A")%>
(
<%=snpRef.getColList("", "[COL_NAME]", ",nt", "", "(INS and REW)")%>
)
select <%=snpRef.getPop("DISTINCT_ROWS")%>
<%=snpRef.getColList("", "[EXPRESSION]", ",nt", "", "(INS and REW)")%>
from <%=odiRef.getSrcTablesList("", "[SCHEMA].[TABLE_NAME]", " FULL OUTER JOIN ", "")%> on <%=odiRef.getJoinList("","([EXPRESSION])"," and ","")%>
where <% if (snpRef.getPop("HAS_JRN").equals("0")) { %>
(1=1)
<%} else {%>
JRN_FLAG <> 'D'
<% } %>
<%=snpRef.getFilter()%>
<%=snpRef.getJrnFilter()%>
<%=snpRef.getGrpBy()%>
<%=snpRef.getHaving()%>
<% } %>

Hope it does the work till latest ODI is released with UNION and MINUS logic.

Download the KM below.

Download XML

One Comment

  1. Hi,

    is there anyway to union two flat files in ODI?, if yes please explain the way in steps.

    Thanks & Regards,
    Manojkumar.

Leave a Reply

Required fields are marked *.