Findind sum of the salary

1. Drag source and target tables into Mapping Designer.
2. Design the mapping as shown below.

3. Connect all ports from Source Qualifier transformation to Expression transformation.
4. Create an output variable in Expression transformation as shown below:
• o_Sum_Sal = CUME(sal)
5. Connect all ports fromm Expression transformation to target.

Continue Reading...

Generating sequence numbers without using Sequence Generator transformation

1. Create a target table with a field name SEQ_NO for sequence number.
2. Design the Mapping as shown above.
3. In the Expression transformation create a variable port and increment it by 1.
• V_Count=V_Count+1
4. In the Expression transformation create a output port and assign variable port to it.
• O_Count=V_Count
5. Connect O_Count to SEQ_NO.

Continue Reading...

Finding Maximum value in the column

Description:
My source and target tables as shown below:

MY SOURCE		MY TARGET
Col_1 Col_2 Col_3 A 0 0 0 B 0 0 0 C		Col_1 Col_2 Col_3 A B C

Solution:

1. Drag source and taget tables in mapping
2. Create an Aggregator t/f and connect all ports from Source Qualifier t/f to Aggregator.
3. In Aggregator t/f, create 3 output ports as shown below:
1. out_Col1=MAX (Col1)
2. out_Col2 = MAX (Col2)
3. out_Col3 =MAX (Col3)
4. Connect all output ports to target table.

5. The mapping pipeline as shown above.

Continue Reading...

Join the data of two tables which does not have common columns

Description:

Lets assume I have two source files as shown below:

SOURCE 1		SOURCE 2
EmpNo Name 100 Sivan 200 Uday 300 Chandu 400 Satish		Sal Location 2000 Noida 3000 Banglore 1800 Hyderabad 1500 Chennai

I would like to load the target a shown below:

SNo	Name	Sal	Location
100	Sivan	2000	Noida
200	Uday	3000	Banglore
300	Chandu	1800	Hyderabad
400	Satish	1500	Chennai

Solution:

1. Drag Source1, Source2, Target tables.
2. Create a Expression t/f and connect SQ_Sorce1 to Expression t/f.
3. Create a variable port say v_Dummy and assign default value as 1.
4. Create a Expression t/f and connect SQ_Sorce2 to Expression t/f.
5. Create a variable port say v_Dummy (with same name and data type used in previous pipeline) and assign default value as 1.
6. Join two piplines to a Joiner t/f. The Joinner condition v_dummy=v_dummy.
7. Connect all ports to the target.

Continue Reading...

How to return muliple values using Unconnected lookup?

Today we discuss how to return muliple values using Unconnected lookup. First of all, the UnConnected lookup has one return port and returns one column from each row. If the interviewer asked the above scenario, follow the below solution.

• Let us assume EMP will be source table and DEPT will be LOOKUP table.
• Create a target table T_UNCONN_LKP in target designer. Table should contain all ports of EMP table plus DNAME and LOC.
• Go to the Mapping Designer and create the mapping with source, target, lookup and expression transformations as shown below:

• Then concatnate multiple ports with the separator '&' into single port in Lookup transformation.

• To do edit Lookup transformation and create a return port (DNAME).
• Then go to Properties tab, there write the following expression in Lookup SQL Over.

SELECT dname||'&'||loc FROM dept

• In expression transformation spilt it into multiple ports as follow.
• Declare 2 output variables with

o_DName = SUBSTR (dname, 1, (INSTR(dname, '&', 1)) o_Loc = SUBSTR(dname, INSTR (dname, '&'))

• Connect all ports from Expression transformation to target table.

Continue Reading...

Split non-key columns

Description: I have a source table with 3 columns EMPNO, ENAME, LOC. Now I would like to split the source into 2 taget tables based on the primary key EMPNO as one target table contains EMPNO, ENAME and another target table contains EMPNO, LOC as shown below.

MY SOURCE		TARGET_1		TARGET_2
SNo Name Loc 100 Chandu Hyderabad 200 Satish Chennai 300 Siva Noida 400 Srinivas Bengaluru		EmpNo EName 100 Chandu 200 Satish 300 Siva 400 Srinivas		EmpNo EName 100 Hyderabad 200 Chennai 300 Noida 400 Bengaluru

Solution:

1. Connect all ports from Source transformation to Aggregate transformation.
2. Connect EMPNO and ENAME ports to one target table.
3. And connect EMPNO and LOC ports to another target table.
4. The final mapping looks like as shown below:

Continue Reading...

Sending second half records of the table to target

1. Drag source and target into the mapping.
2. Let us assume EMP is source, and create a new table say T_EMP with same structure as EMP.
3. Create the mapping as shown below:

4. Now edit Source Qualifier tranformation and click on properties tab.
5. Enter the following query in SQL Query property:

SELECT * FROM emp

MINUS

SELECT * FROM emp WHERE ROWNUM <= (SELECT COUNT(*)/2 FROM emp

6. Save the mapping.
7. Finally create session, Workflow and run thee workflow.

Continue Reading...

Sending first half records of the table to target

1. Drag source and target into the mapping.
2. Let us assume EMP is source, and create a new table say T_EMP with same structure as EMP.
3. Create the mapping as shown below:

4. Now edit Source Qualifier tranformation and click on properties tab.
5. Enter the following query in SQL Query property:

SELECT * FROM emp WHERE ROWNUM <= (SELECT COUNT(*)/2 FROM emp

6. Save the mapping.
7. Finally create session, Workflow and run thee workflow.

Continue Reading...

Concatinate duplicate values

MY SOURCE		MY TARGET
SNo Name 1 A 2 B 1 C 3 D 4 F 1 E 2 G		SNo Name 1 A 1 AC 1 ACE 2 B 2 BG 3 D 4 F

Solution:

• Here the no. of rows in source and target tables are same; so here we think about Passive transformations.
• After importing Source and Target, create a Sorter transformation to sort the data.
• Then create an Expression transformation with 3 ports named say v_Name, v_SNo (2 variable ports) and o_Name (output port).
• Give the expressions as follows
• v_Name as variable port

IIF(SNo = v_SNo, v_Name||Name, Name)

• v_SNO as variable port

SNo

• o_Name as output port

v_Name

• The final mapping looks like..

• Finally connect the ports to Target, create and execute the session and workflow.

Continue Reading...