SQL Transformation in Informatica Example Using Static SQL query

Q1) Let’s say we have the products and Sales table with the below data.

Table Name: Products
PRODUCT 
-------
SAMSUNG
LG
IPhone

Table Name: Sales
PRODUCT QUANTITY PRICE
----------------------
SAMSUNG 2        100
LG      3        80
IPhone  5        200
SAMSUNG 5        50

Create a mapping to join the products ant sales table on product column using the SQL Transformation? The output will be

PRODUCT QUANTITY PRICE
----------------------
SAMSUNG 2        100
SAMSUNG 5        500
LG      3        80

Solution: 

Just follow the below steps for creating the SQL transformation to solve the example

• Create a new mapping, drag the products source definition to the mapping.
• Go to the toolbar -> Transformation -> Create -> Select the SQL transformation. Enter a name and then click create.
• Select the execution mode as query mode, DB type as Oracle, connection type as static. This is shown in the below image.Then click OK.

• Edit the sql transformation, go to the "SQL Ports" tab and add the input and output ports as shown in the below image

• In the same "SQL Ports" Tab, go to the SQL query and enter the below sql in the SQL editor.

  >>> select product, quantity, price from sales where product = ?product?

• Here ?product? is the parameter binding variable which takes its values from the input port. Now connect the source qualifier transformation ports to the input ports of SQL transformation and target input ports to the SQL transformation output ports. The complete mapping flow is shown below.