Scenario 21: How to do Union without using Union Transformation

Scenario 21: How to do Union without using Union Transformation?

Let's Chat: Union is horizontal merge of tables whereas Join is Vertical Merge of tables. As we have to merge two tables data without using Union means we have to achieve Horizontal Merge Logically.

Theory: We will join two tables first.. suppose we have 8 columns in table A and 8 columns in table B so, we will get total 16 columns after join. Now if we can transpose or convert the last 8 columns in row then we are done. Yes, you are correct using Normalizer. Few more things we need to take care that we will see practically. I am taking Oracle default EMP table two times for practical.

Practically:
Step 1: Drag and Drop your both source table in Mapping Designer.

Step 2: 
Expression (For First Source Qualifier)
Create four ports.
ID (Variable Port) = OLD_ID + 1
OLD_ID (Variable Port) = ID
OUT_ID (Output Port) = ID
OUT_HIREDATE (OutPut Port) = TO_CHAR(HIREDATE, 'MM/DD/YYYY')

Note: We are converting HIREDATE to char because we can't use DATE data type in Normalizer to transpose. 

Step 3: 
Expression (For second Source Qualifier)
Create four ports.
ID (Variable Port) = OLD_ID + 1
OLD_ID (Variable Port) = ID
OUT_ID (Output Port) = ID
OUT_HIREDATE (OutPut Port) = TO_CHAR(HIREDATE, 'MM/DD/YYYY')

Note: We are converting HIREDATE to char because we can't use DATE data type in Normalizer to transpose. 

Step 4
Joiner
Connect all columns from Expression 1 and 2
 Join Type :- NORMAL
Join Condition :- ID_OUT1=ID_OUT

Step 5
Normalizer
Create 8 ports in Normalizer Tab
Set OCCURS 2 for each Column and be careful with data type of each column.

COLUMN  OCCURS DATATYPE
EMPNO    2     NUMBER
ENAME    2     STRING
JOB      2     STRING
MGR      2     NUMBER
HIREDATE 2     STRING
SAL      2     NUMBER
COMM     2     NUMBER
DEPTNO   2     NUMBER

Step 6
Expression
Create one output port to convert HIREDATE from string to Date.

OUT_HIREDATE (Output Port) IIF( IS_DATE(HIREDATE, 'MM/DD/YYYY'), TO_DATE(HIREDATE, 'MM/DD/YYYY'))

Step 7
Connect your target. Create session and workflow, Run it and have same output of Union.

BINGO!!!

Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com