Wednesday 4 June 2014

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

14 comments:

  1. Hi Harsh,
    Sorry if this seems stupid question but just to confirm:
    ID (Variable Port) = OLD_ID + 1
    Is ID equal to emp number in EMP table?

    ReplyDelete
    Replies
    1. No, EMPNO and ID is not equal. I have created ID for both source to join these tables. So, here ID is working as common column with VALUE which is present in both tables. Though EMPNO is also present in both table but you won't have common data for EMPNO to join.

      Hope now it's clear.. Let me know if you still have doubt.
      Thanks.

      Delete
  2. Its not working.problem with here
    OUT_HIREDATE (OutPut Port) = TO_CHAR(HIREDATE, 'MM/DD/YYYY').
    Better give me some other example

    ReplyDelete
    Replies
    1. Hi, Vishnu..

      Simply saying it's not working is not going to help us. Tell me the error which error you are getting.
      It's implemented and tested code. You must be doing mistake somewhere.
      Let me know your error with description. Or If you want .xml file of mapping drop me a mail.

      Thanks.

      Delete
    2. I didn't get any session log.I got only workflow logs.
      Session task instance : Execution failed.Thread:3844

      Delete
    3. So, for this error check list...
      Check your mapping is valid or not ?
      Check is your session is impacted?
      Check your relational connection for source and target.

      If all checks passed and still your are not able to figure out your error send me your mapping.

      Delete
  3. hi harsh pandey ,
    instead of creating variable port to join can we use dummy port in both expression t/r to join both tables

    ReplyDelete
  4. I have a scenario please help me
    My source have a field name cusip 10 times like cusip1 cusip2 so on and one more column amount i want to convert cusip columns in row and amount column need to divided by number of cusip column have value

    ReplyDelete
  5. Might be some cusip don't have value

    ReplyDelete
  6. For example only two cusip column have value then amount column will be divided by 2 only
    Please help me thanks in advance

    ReplyDelete
    Replies
    1. Hi Rajeev

      I am assuming that you have fixed column for CUSIP1.... CUSIP10 then you have Amount.

      As your columns are fixed. Use Normalizer Transformation with occurs 10 then use Aggregator Transformation use GROUP BY based upon CUSIP column and do the SUM(AMOUNT) it will solve your scenario.

      For further assistance please use my email id hpandey.harsh@gmail.com

      Thanks

      Delete
  7. We can implement same with java transformation also

    ReplyDelete