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.

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.

Stored Procedures

Stored procedures provide a powerful way to code application logic that can be stored on the server. Stored procedures reduce network traffic, improve performance, and improving productivity (statements in a stored procedure only need to be written one time). Additionally, stored procedures can be used to help ensure the integrity of the database (information is entered in a consistent manner).

In a database management system (DBMS), a stored procedure is a set of Structured Query Language (SQL) statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs. The use of stored procedures can be helpful in controlling access to data (end-users may enter or change data but do not write procedures), preserving data integrity.

The syntax for a procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [,parameter]) ] IS [declaration_section] BEGIN executable_section [EXCEPTION exception_section] END [procedure_name];

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

• IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
• OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
• IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.

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