Transcript

Scenario1

:

Calculate the TAX for each employee who belongs to the sales department if sal
is greater than 5000 then calculate the tax as sal*0.17 else calculate the TAX as
SAL*0.13

Note: Sales Department identified with deptno=30

Source:

Target:

Logic: use filter stage to filter the rows where deptno=30 and create a new
column in Transformer ie.TAX and write the derivation which is given below

TAX=If DSLink5.SAL >5000 Then DSLink5.SAL * 0.17 Else DSLink5.SAL*0.13

Job Design:

Scenario2:

Calculate the total salary for each employee based on SAL&COMM(Comm col
may have Nulls)

Source:

Target:

Logic: Create a new column in Transformer ie.Totalsal and write the derivation
which is given below

If IsNull(DSLink4.COMM) Then DSLink4.SAL Else DSLink4.SAL + DSLink4.COMM

Job Design:

ANNUALSAL based on Total salary the total salary calculated based on SAL&COMM .Scenario3: Create a job using filter stage to load the data which is having job as analyst Source: Target: Logic: In filter stage write the where condition JOB='ANALYST' Job Design: Scenario4: Calculate the TAX.HRA.

25 ELSE Calculate the TAX as TOTSAL*0.sal.COMM Create three new columns with Names TAX.18 Calculate the ANNUALSAL as follows TOTSAL*12 Source: Target: Logic: Create a Stage variable in transformer TOTSAL and do the derivation which is given below TOTSAL=If IsNull(DSLink4.SAL + DSLink4.15 Calculate the HRA as follows TOTSAL*0.TOTSAL=SAL+COMM(Comm col may have nulls) If(is null(comm.SAL Else DSLink4.ANNSAL and write the derivation for each column which is given below .sal+comm) If totalsal greater than 8000 then calculate the tax as TOTSAL*0.HRA.COMM) Then DSLink4.

25 Else TOTSAL*0.TAX=If TOTSAL> 8000Then TOTSAL*0.18 ANNSAL=TOTSAL*12 Job Design: Scenario5: Scenario1.15 HRA=TOTSAL*0. Cummilative Sum: Source: Target: .

SAL Scenario6: Each Department wise Highest and Lowest Salary required Source: .Job Design Logic: Create stage variable VCUMMSUM and assign initial value=0 VCUMMSUM=VCUMMSUM + DSLink3.

Target: SQL query: Select deptno.min(sal) min_max from emp group by deptno Union Select deptno. min_max from ( Select deptno.min(sal) min_max from emp where deptno=10 group by deptno union select deptno.max(sal) min_max from emp where deptno=10 group by deptno union select deptno.max(sal) min_max from emp group by deptno) Second query: select deptno.min(sal) min_max from emp where deptno=20 group by deptno union .

min(sal) min_max from emp where deptno=30 group by deptno union select deptno.HRA.TAX GROSS SAL=TOTSAL+HRA-TAX TOTSAL=SAL+COMM Comm my have nulls .max(sal) min_max from emp where deptno=20 group by deptno union select deptno.select deptno.max(sal) min_max from emp where deptno=30 group by deptno Job Design: Scenario7: Calculate the GROSS SAL for each employee based on TOTSAL.

SAL*0.HRA.HRA and do the derivation which is given below TOTSAL=If IsNull(DSLink4.SAL*0.18 And Create three new columns in transformer TAX.17 Source: Target Logic: Create a Three Stage variable in transformer TOTSAL .HRA=SAL*0.TAX.COMM TAX=DSLink4.SAL Else DSLink4.SAL + DSLink4.TAX Scenario8: Converting Rows into Columns without using pivot: Source: Target: .COMM) Then DSLink4.17 HRA=DSLink4.18 TAX=SAL*0.GROSSSAL TAX=TAX HRA=HRA GROSSSAL=TOTSAL + HRA.

Job Design: Job Logic: In Transformer Change the Field Name MONTH1 to MONTH Similary for MONTH2.MONTH3 And copy Three Transformer output to Funnel Scenario9: Fetch First and Last record from a file Source: Target: Job Design: Job Logic: Take one record from head and take one record from Tail and club these two at target .

Scenario 10 How to send first half record to target Source: Target: JOB: Job Logic:In source DB write the query given below select * from emp where rownum <= ( select count(*)/2 from emp) Scenario 11: How to send second half record to target: .

Source: Target: Job Design: Job Logic: in source DB write the query given below Select * from emp minus Select * from emp where rownum <= ( select count(*)/2 from emp) Scenario 12: How to send Even record in to one target &Odd records in to another target: .

Source: Target: Even: Odd: Job Design JobLogic: .

20. Source: Target1: Target2: Target3: Job Design: .Create Stage variable with name Flag and enter following derivation If Mod(DSLink2. Separate the record to different target department wise.2)=0 Then "EVEN" ELSE "ODD"=FLAG And in Transformer Constraints Even link apply the condition FLAG="EVEN" Similarly for Odd constraint apply the condition FLAG="ODD" Scenario13: Scenario 9: In Emp table there are three departments (dept no 30.EMPNO.10).

Job Logic: In transformer constraint apply the below conditions Scenario6: Create a job that can load employees who employee no is EVEN Scenario7: Create a job that can load employees who employee no is ODD Scenario8: Design a job that can load employees whose employee name start with ‘S’ or ‘J’ Scenario9: Design a job that can generate sequence no Scenario10: Design a job that can load alternative records in to target Scenario11: Design a job that can load load 5th and 8th record into atarget .

Scenario12: Source: Custid Cust_Name Gender 7369 SUBHA 0 7499 SURESH 1 7566 JAME SMEENA 2 Target: Custid Cust_Name Gender 7369 SUBHA F 7499 SURESH M 7566 JAME SMEENA UNK Scenario13: Source Cust_FNa Custid me Cust_LName City 7001 BILL CLINTON HYD 7002 NARA LOKESH BANG 7003 SONIA GANDHI DELHI 7004 VENKAT RAVI VIZAG Target: Custid Cust_Name City 7001 BILL CLINTON HYD 7002 NARA LOKESH BANG SONIA 7003 GANDHI DELHI 7004 VENKAT RAVI VIZAG Scenario14: .

COM SUJANA [email protected]: Cust_FNa Custid me Email 7001 VENKAT [email protected] 7003 KRSIHNA M Target: Cust_FNa Custid me Email Username 7001 VENKAT [email protected] 7002 SUJANA [email protected] [email protected] 7003 KRSIHNA M KRISHNA Scenario15: Source: Custid Cust_Name 7001 VENKAT RAVI 7002 SRIDHAR REDDY 7003 SOBHA RANI Target: Custid Cust_FName Cust_LName 7001 VENKAT RAVI 7002 SRIDHAR REDDY 7003 SOBHA RANI Scenario16: Source: Custid Cust_Name VENKAT RAVI 7001 KUMAR RAVI RAJA 7002 PINISETTI ‘ .COM VENKAT 7002 SUJANA SUJANA@YAHOO.

T3 emp name start with “S” pass to “T1” AND emp name start with “J” pass to “T2” AND others pass to “T3” Scenario19: There are twotarget tables with name T1.T2 and 2 nd record pass to “T1” AND 8 th record pass to “T2” Scenario20: There are two target tables with name T1.T2 And alternative records pass to “T1” AND Others pass to “T2” Scenario21: sourcedata Colum n 10 10 10 20 20 .Target: Cust_MNa Custid Cust_FName Cust_MName me 7001 VENKAT RAVI KUMAR 7002 RAVI RAJA PINISETTI Scenario17: Source: EMPID PHONE 7369 9948047694 7370 9912395605 7371 8600097130 Target: EMPID PHONE 7369 (994)-80-476-94 7370 (991)-23-956-05 7371 (860)-00-971-30 Scenario18: There are three target tables with name T1.T2.

20 30 30 Targetdata: Colum Colum n1 n2 10 1 10 1 10 1 20 2 20 2 20 2 30 3 30 3 Scenario22: sourcedata Colum n 10 10 10 20 20 20 30 30 Targetdata: Colum Colum n1 n2 10 1 10 2 10 3 20 1 20 2 .

20 3 30 1 30 2 Scenario23: sourcedata Colum n 10 10 10 20 20 20 30 30 Target data: Colum Colum n1 n2 10 1 10 2 10 3 20 4 20 5 20 6 30 7 30 8 Scenario23: Source data: Colum n 10 10 .

10 20 20 20 30 30 Target data(T1) Colum n1 10 20 30 Target data(T2) Colum n2 10 10 20 20 30 Scenario24: There are two sources with Name S1&S2 and S1 Having 5 fields and S2 haing 3 fields there is no common column Scenario25: Sourcedata Empid Sal 1 1000 2 2000 3 3000 4 5000 Target data: Empid Sal Salary_Sum 1 1000 11000 2 2000 11000 .

3 3000 11000 4 5000 11000 Scenario26: Calculate the amount of salary paying for each department Scenario27: If source contains N-rows Load N+5 Rows into the target Scenario28: If source contains N-rows where N=EVEN Load First half records into the target Query: Select * from emp where rownum<=(select count(*)/2 from emp) Scenario29: If source contains N-rows where N=EVEN Load Second half records into the target Query: Select * from emp Minus Select * from emp where rownum<=(select count(*)/2 from emp) Scenario30: Sourcedata Empid Sal 1 1000 2 2000 3 3000 4 5000 Target data: Empid Sal Salary_Sum 1 1000 1000 2 2000 3000 3 3000 6000 4 5000 11000 .