SQL Transformation 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        500

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

PRODUCT QUANTITY PRICE
----------------------
SAMSUNG 2        100
SAMSUNG 5        500
IPhone  5        200
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. Here for all the ports, you have to define Data Type (informatica specific data types) and Native Type (Database specific data types).

• 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.

• Create the workflow, session and enter the connections for source, target. For SQL transformation also enter the source connection.

After you run the workflow, the integration service generates the following queries for sql transformation

select product, quantity, price from sales where product ='SAMSUNG'
select product, quantity, price from sales where product ='LG'
select product, quantity, price from sales where product ='IPhone'

Dynamic SQL query: A dynamic SQL query can execute different query statements for each input row. You can pass a full query or a partial query to the sql transformation input ports to execute the dynamic sql queries.
SQL Transformation Example Using Full Dynamic query
Q2) I have the below source table which contains the below data.

Table Name: Del_Tab
Del_statement
------------------------------------------
Delete FROM Sales WHERE Product = 'LG'
Delete FROM products WHERE Product = 'LG'

Solution:
Just follow the same steps for creating the sql transformation in the example 1.

• Now go to the “SQL Ports” tab of SQL transformation and create the input port as “Query_Port”. Connect this input port to the Source Qualifier Transformation.
• In the “SQL Ports” tab, enter the sql query as ~Query_Port~. The tilt indicates a variable substitution for the queries.
• As we don’t need any output, just connect the SQLError port to the target.
• Now create workflow and run the workflow.

SQL Transformation Example Using Partial Dynamic query
Q3) In the example 2, you can see the delete statements are similar except Athe table name. Now we will pass only the table name to the sql transformation. The source table contains the below data.

Table Name: Del_Tab
Tab_Names
----------
sales
products

Solution:
Create the input port in the sql transformation as Table_Name and enter the below query in the SQL Query window.

Delete FROM ~Table_Name WHERE Product = 'LG'