· Active Transformation
· Joiner relates two or more tables.
· It allows homogeneous or heterogeneous sources.
· Join Types
NORMAL (Equijoin – default)
OUTER
ü Master Outer
ü Detail Outer
ü Full Outer
· Does not allow non-equi joins.
Join Types
Equi-Join
Example
EMP (DETAIL )
Empno ename sal deptno
1 x 2000 10
2 y 3000 20
3 z 1000 10
4 A 3000
DEPT (MASTER)
DEPTNO DEPTNAME
10 HR
20 FI
30 SALES
Equi-Join (3 Records)
1 x 2000 10 HR
2 y 3000 20 FI
3 z 1000 10 HR
MASTER OUTER
Equi-Join + Remaining Records of Detail (4 records)
1 x 2000 10 HR
2 y 3000 20 FI
3 z 1000 10 HR
4 A 3000
Detail Outer
Equi-Join + Remaining Records of MASTER (4 records)
1 x 2000 10 HR
2 y 3000 20 FI
3 z 1000 10 HR
30 SALES
Full Outer
Equi-Join + Remaining Records of MASTER + Remaining Records of Detail (5 records)
1 x 2000 10 HR
2 y 3000 20 FI
3 z 1000 10 HR
30 SALES
4 A 3000
Interview Question for JOINER
Does JOINER allow non-equi-join?
Ans: NO It does not allow non-equi-joiN.
Create a table DIM_JOIN in the DWH_MEEN schema.
CREATE TABLE DIM_JOIN AS
SELECT * FROM EMP
WHERE 1=2;
Table structure only gets copied.
Alter the table and add a new column DNAME.
SQL> ALTER TABLE DIM_JOIN
ADD DNAME VARCHAR2(30);
Table altered.
SQL> DESC DIM_JOIN;
Name Null? Type
----------------------------------------------------- -------- ---------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
DNAME VARCHAR2(30)
SQL> COMMIT;
Import that table in the Warehouse Designer.
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
9999 MONA ANALYST 7566 06-AUG-09 2000
14 rows selected.
SQL> select * from scott.dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 SUPPORT BANGALORE
60 DISTRIBUTION DELHI
70 EDUCATION CHENNAI
80 XYZ ABC
8 rows selected.
STEPS:
1. Define Sources (EMP,DEPT)
2. Define Target (DIM_JOIN)
3. Define Mapping (M_JOIN)– Drag two sources and one target in Mapping Designer Workspace.
4. From the Transformation Menu select JOINER and create. T_JOIN
Drag the required ports from two source qualifiers to the JOINER.
Note: The first input to JOINER becomes Master and next Detail. (??? I got the detail as detail and master as master only )
Go to JOINER,edit – go to PORTS tab and select master port for deptno,dname and loc.
Ports: Input,Output,MAster
Go to the Conditions tab and add a new condition.
Go to the Condition Tab and add a condition.
From the master table and detail table select the common column.
(Common column with common data type and size)
We can also add multiple conditions in a JOIN condition.
Properties:
· Data Cache
· Index Cache is going to use JOIN condition.
· Sorted Input
· Join Type
· Join Condition
· Cache Directory
· Case sensitive string comparison
· Join Type : NORMAL
· NULL ordering in MASTER/DETAIL (By default it is high – NULL values will come at the bottom)
Project the required ports from JOINER to TARGET.
Validate the mapping.
Repository -> Save
Define session, workflow WF_JOIN.
Start workflow and preview the output.
NOTE: For JOINER and some other transformations, we can edit the properties at the Session level also.
Edit the Session and go to Mappings tab.
Select Transformations.
Master outer
(Equijoin + Remaining records of Detail Table)
Select the Join Type as Master Outer.
Repository -> Save.
Output (13 + 1)
DETAIL OUTER JOIN
Equijoin + Remaining Records of Master Table
(13 + 5 = 18)
Repository -> Save
Output:
FULL OUTER JOIN
(Equijoin + Remaining Records of Master + Remaining Records of Detail)
(13 + 5 + 1 = 19)
Output:
Comments
Post a Comment