Join needs Same key Column Name When we join 2 tables For ex:-EMP and Dept In join, While mapping If Join type = Inner/left outer join, then Dept no while joining comes from left If join type = right outer join, then deptno comes from right If join type = full outer join, then deptno comes from both left and right If join type = Right outer join , then Dept no comes from Right Note :- Full outer join Can take only 2 inputs, that is it can’t join any 3 rd table
Join Key = Dept no Options Join type = InneràOutputDo Mapping Join type = Inner / left outer/Right outer/ fullouter Output EMP No E Name Job Mgr Hire date Sal Comm Dept no D Name loc
Oracle 0 Load EMP table oracle 1 Load EMP table columnsStage any columns Name Copy :- Output ColumnsChange the column Name Join Key = EMP No Join type = INNER Output Do Mapping Output EMP No E Name Job Mgr Hire date Sal Comm Dept no Job 3 T1 : C id, E name, d no T2:D no, D name, loc id T3:loc id , loc Name, area id T4: Area id , add1, add2 Output EID No E Name Dept no D Name loc id loc name area id add1 add2 first load T1, T2 and perform join 0 key = dno Now ,load T3 and perform join1, key =load load T4 and perform join 2 key = area id Compile and Run Output :- Area id , add1,add2,loc id , loc name, d no, d name , c id , e name job 4 If we have 2 tables with same structure and same column Names E1
E id | E name | Add |
11 | A | HYD |
22 | B | SEC |
E2
E id | E name | Add |
11 | A | DEL |
22 | B | GNT |
Output A A HYD DEL B B SEC GNT change the column Name in any one of the table using copy operator with out changing key column name Copy OutputDo mappingColumnsChange E name to E name 1 Join Key = c id Do mapping Output
E id | E name | Add | E name | Add |
11 | A | HYD | A | DEL |
22 | B | SEC | B | GNT |
changing the column Name without using copy operator Load EMP1, EMP2 Select Read Method = user defined SQL= Select EMP no, E name, as E name , job as job 1, mgr as magr1, hire date as Hire data1 , Sal as Sal1, comm. As comm. From EMP columns change Names Here also Output E id E name job mgr E name 1 job 1 mgr 1 …….. job 5 :- 2 Tables with out key dependency EMP E id E name Add 11 A HYD 22 B SEC SRC SRC- Name TBL – Name Kindle CRM – CUST Create an column for key dependency using column Generator. Create a new column ,in both the column generator Column generate = Dummy Algorithm = cycle Value = 1 Output E id E name Add Src - Name TABLE – Names 11 A HYD Kindle CRM – CUST 22 B SEC kindle CRM – CUST
Aspired to become an DataStage? Explore the post to discover the know-hows on DataStage Training.
Merge It has two Modes
Master table :- (Stable info) Stable which are not changes frequently. P id, prd. Desc, prd -manuf 11, Linux, Tata 22, Santoor, Wipro 33, Wipro, unilever 44, Margo, unilever up date 1 P id, prd – age , prd – price 11, 4, 87 22, 2, 42 55, 1, 35 66, 7, 90 update 2 :- Pid, prd – supplier, prd – Category 11, hind, health 33, Tata, Nutri 77, Bagan , Health 88, recom, Home job 1 Merge
Key = p id Sort order = Ascending Options Un matched master mode = Drop / keep Warn on Reject updates = True Warn on un matched master = True Click on the link ordering Output Do mapping Output Data set 0:
PID | Prod_desc | Prod_manuf | Prd_age | prdprice | Prd_supplier | Prd catagory |
11 | Lux | TATA | 4 | 87 | Hind | health |
22 | Samtoor | Wipro | 2 | 42 | ||
33 | wipro | Uniliver | 0 | 0 | tata | utri |
Pid | Prd age | Prd price |
55 | 1 | 35 |
66 | 7 | 90 |
pid | Prd supplier | Prd cat |
77 | bagan | health |
99 | Re com | Home |
De- Duplication :- (Removing Duplicate records) C id E name 11, a 11, b 22, c 11, d 33, u 22, n 33, i 11, t 22, l
Remove Duplicates
E id E name 11 a 11 b 11 d 22 b 22 n 22 l 33 i 33 u
Output E id , E name 11, a 22, b 33, i Aggregator Aggregator is a processing Stage which works on groups and perform counting and calculations supports 1 input and 1 output. (a) job :- Need to find the no .of duplicates Aggregate type = Count rows Count output column = dept . count
Allow Null output = false Method = hash output Do Mapping Output E id dept – count 11 3 22 3 33 2 (b) fork join :-
Output Do mapping for job, Aggregator Aggregator Group = c id Aggregation type = Count Rows Count output column = dept – countoutputDo mapping Join Key = c id output Do mapping output
cid | cname | Dept_count |
11 | A | 3 |
11 | B | 3 |
11 | D | 3 |
22 | B | 3 |
22 | N | 3 |
22 | L | 3 |
33 | I | 2 |
33 | u | 2 |
job Aggregation Type = calculation Column for calculation = Sal Maximum value output column = MAX Sal Minimum value output column = MIN Sal SUM output column = total output DO mapping Output
Dept No | Max Sal | Min Sal | total |
10 | 25000 | 21300 | 68750 |
20 | 23000 | 20800 | 110875 |
30 | 22850 | 20950 | 129400 |
job Copy :- output Do Mapping Aggregator Group = dept no Aggregation Type = Calculation Column for calculation = Sal =Max Sal = Min Sal = tat Sal output Do mapping Column generator Column to generate = companyMappingValue = IBM Aggregator 1 Group = Company Column for calculation = Sal = Max Sal Output Mapping Output Data set 1 Company Max Sa IBM 25000 job All the Employee details of an particular dept no. should be display Copy :- output Do Mapping Aggregator Group = dept no Aggregation Type = Calculation Column for calculation = Sal =Max Sal = Min Sal = tat Sal Join Key = e id output DO mapping Remove Duplicates Key = c idDuplicate to Retain = firstOutputDo Mapping
You liked the article?
Like : 0
Vote for difficulty
Current difficulty (Avg): Medium
1/4
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.
Stay Updated
Get stories of change makers and innovators from the startup ecosystem in your inbox