In this entry , i will explain how to make UNION , UNION_ALL, INTERSECT style operations without making extra insertion or temp table creation.
*Case : What i want to do is to gather all employee information which currently holds in different tables.
( Tested enironment : Oracle 10g database , ODI 11g )
At first we will create a table which holds the SQLs for final statement.
CREATE TABLE REPORTING.INTERFACE_QUERY
INTERFACE_NAME VARCHAR2(2000 BYTE),
* This table contains two columns ,one interface name which holds the name of the interface and EXE_SCRIPT which holds the SQL created by ODI interface.
*What we gonna do is to extract source tables called EMPLOYEE_PRE , EMPLOYEE_VICE_PRE, EMPLOYEE_PRG and fill them into a table called EMPLOYEE_ALL
*Now creating interfaces which are their target tables are dummy ( yellow interfaces )
* In this interface we will add EMPLOYEES who are currently presidents and with the help of IKM knowledge module called IKM SQL Control Append-SubQuery
Link for Download
SQL statement which is created by the interface will be inserted into REPORTING.INTERFACE_QUERY table.Same operation will be done for vice president and programmer tables .
As a result we have following data in our reference table ( Interface_Query ) and we have 3 temp interface.
*Now what we have to do create a main interface which gathers all sql statements above and load data into our target table.
*I will prepare the allignment of the columns for my target table which has to be matched with my subquery columns.
Then i created a new interface called Interface_main. Mapping looked like this
* I drag 3 temp interfaces as a source and in order to maintain integrity connecting them with dummy join ( department_name ) and adding my target table with mappings ( those mapping added as dummy too )
* now i am assigning the knowledge module called IKM SQL Control Append-Union-Minus-Intersect-Union All
Link for Download
*Parameters for this Knowledge Module look like this
*Basically knowledge module gathers SQL statements from REPORTING.INTERFACE_QUERY and depends on the parameter we choose, it makes UNION , UNION ALL or INTERSECT and creates a resultset which load into our target table. As a result following sql statement has been created and executed.