Scenario 24: How to Join two tables EMP and DEPT without using Joiner, Source Qualifier and Lookup. (Level 2)

Scenario 24: How to Join two tables EMP and DEPT without using Joiner, Source Qualifier and Lookup. (Level 2)

CASE 1
 For Employees data you are getting EMPNO, ENAME, JOB, MGR in one table let's say EMP1 and EMPNO, SAL, COMM, DEPTNO in another table let's say EMP2. 
http://informaticachamp.blogspot.in/2014/06/scenario-23-how-to-join-two-tables.html

Theory: 
We need Union to merge two source then sort the data based upon Deptno and will use expression to retain DNAME and LOC value for next row if deptno is same for current row and next row. Confused? Even I am. Let's Do it Practically.

Practically.

I am taking default Oracle Table EMP and DEPT. Hope everyone know the table structure of these two table so, I am not giving structure.

Step 1:
Drag and Drop your source EMP and DEPT.

Step 2:
Expression
Connect from EMP source qualifier to expression.
Create two ports

DNAME (Output Port) = NULL
LOC       (Output Port) = NULL

Note: Take care of Data Type, Precision and Scale while creating. It should match to Dept table.

Step 3
Expression
Connect from DEPT source qualifier to expression
Create seven ports

EMPNO (Output Port) = NULL
ENAME (Output Port) = NULL
JOB        (Output Port) = NULL
MGR       (Output Port) = NULL
HIREDATE  (Output Port) = NULL
SAL         (Output Port) = NULL
COMM  (Output Port) = NULL

Note: Take care of Data Type, Precision and Scale while creating it should match to EMP table.

Step 4
Union
Create two groups and group tab EMP and DEPT
Connect Expression 1 to EMP group and Expression 2 to DEPT group.

Step 5
Sorter
Connect Union to Sorter

Select DEPTNO as Key Port. 

In the Properties tab select check box "NULL treated low"

Step 6
Expression
Connect Sorter to Expression

NEW_REC_FLAG (Variable Port) = IIF (ISNULL(EMPNO),1,0)

DEPTNO_COMPARE_FLAG (Variable Port) = IIF(DEPTNO !=OLD_DEPTNO, 1, 0)

DEPTNO_COMPARE (Variable Port) = IIF(DEPTNO=OLD_DEPTNO, OLD_DEPTNO, DEPTNO)

OLD_DEPTNO (Variable Port) = DEPTNO_COMPARE

DNAME_COMPARE (Variable Port) = IIF(DEPTNO_COMPARE_FLAG=1, DNAME, OLD_DNAME)

OLD_DNAME (Variable Port) = DNAME_COMPARE

LOC_COMPARE (Variable Port) = IIF(DEPTNO_COMPARE_FLAG=1, LOC, OLD_LOC)

OLD_LOC (Variable Port) = LOC_COMPARE

OUT_DNAME (Output Port) = DNAME_COMPARE

OUT_LOC (Output Port) = LOC_COMPARE

NEW_REC_FLAG_OUT (Output  Port) = NEW_REC_FLAG

Step 7
Filter
Filter Condition: NEW_REC_FLAG_OUT=0

Step 8
Connect filter to Target. 

BINGO!!!

Feel free to post your doubt in comment section or though contact form which you can find below the page.

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