SQL Transformation in Informatica with examples

  • Updated
  • Posted in Programming
  • 5 mins read


SQL Transformation is a related transformation used to course of SQL queries in the midstream of a pipeline. We can insert, replace, delete and retrieve rows from the database at run time utilizing the SQL transformation.

The SQL transformation processes exterior SQL scripts or SQL queries created in the SQL editor. You may move the database connection data to the SQL transformation as an enter information at run time.

The following SQL statements can be utilized in the SQL transformation.

  • Data Definition Statements (CREATE, ALTER, DROP, TRUNCATE, RENAME)
  • DATA MANIPULATION statements (INSERT, UPDATE, DELETE, MERGE)
  • DATA Retrieval Statement (SELECT)
  • DATA Control Language Statements (GRANT, REVOKE)
  • Transaction Control Statements (COMMIT, ROLLBACK)

Configuring SQL Transformation

The following choices can be utilized to configure an SQL transformation

  • Mode: SQL transformation runs both in script mode or question mode.
  • Active/Passive: By default, SQL transformation is an energetic transformation. You can configure it as passive transformation.
  • Database Type: The kind of database that the SQL transformation connects to.
  • Connection kind: You can move database connection data or you should use a connection object.

We will see learn how to create an SQL transformation in script mode, question mode and passing the dynamic database connection with examples.

Creating SQL Transformation in Query Mode

Query Mode: The SQL transformation executes a question that outlined in the question editor. You can move parameters to the question to outline dynamic queries. The SQL transformation can output a number of rows when the question has a choose assertion. In question mode, the SQL transformation acts as an energetic transformation.

You can create the next sorts of SQL queries

Static SQL question: The SQL question assertion doesn’t change, nonetheless you possibly can move parameters to the sql question. The integration service runs the question as soon as and runs the identical question for all of the enter rows.
Dynamic SQL question: The SQL question assertion and the information can change. The integration service prepares the question for every enter row after which runs the question.

SQL Transformation Example Using Static SQL question

Q1) Let’s say we have now the merchandise and Sales desk with the under information.

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 hitch the merchandise and gross sales desk on product column utilizing the SQL Transformation? The output will probably be

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

Solution:

Just comply with the under steps for creating the SQL transformation to unravel the instance

  • Create a brand new mapping, drag the merchandise supply definition to the mapping.
  • Go to the toolbar -> Transformation -> Create -> Select the SQL transformation. Enter a reputation after which click on create.
  • Select the execution mode as question mode, DB kind as Oracle, connection kind as static. This is proven in the under picture.Then click on OK.
informatica sql transformation in query mode
  • Edit the sql transformation, go to the “SQL Ports” tab and add the enter and output ports as proven in the under picture. Here for all of the ports, you need to outline Data Type (informatica particular information varieties) and Native Type (Database particular information varieties).
sql transformation sql ports tab in informatica
  • In the identical “SQL Ports” Tab, go to the SQL question and enter the under sql in the SQL editor.
choose product, amount, worth from gross sales the place product = ?product?
  • Here ?product? is the parameter binding variable which takes its values from the enter port. Now join the supply qualifier transformation ports to the enter ports of SQL transformation and goal enter ports to the SQL transformation output ports. The full mapping move is proven under.
sql transformation informatica mapping
  • Create the workflow, session and enter the connections for supply, goal. For SQL transformation additionally enter the supply connection.

After you run the workflow, the combination service generates the next queries for sql transformation

choose product, amount, worth from gross sales the place product="SAMSUNG"
choose product, amount, worth from gross sales the place product="LG"
choose product, amount, worth from gross sales the place product="IPhone"

Dynamic SQL question: A dynamic SQL question can execute totally different question statements for every enter row. You can move a full question or a partial question to the sql transformation enter ports to execute the dynamic sql queries.

SQL Transformation Example Using Full Dynamic question

Q2) I’ve the under supply desk which incorporates the under information.

Table Name: Del_Tab
Del_statement
------------------------------------------
Delete FROM Sales WHERE Product="LG"
Delete FROM merchandise WHERE Product="LG"

Solution:

Just comply with the identical steps for creating the sql transformation in the instance 1.

  • Now go to the “SQL Ports” tab of SQL transformation and create the enter port as “Query_Port”. Connect this enter port to the Source Qualifier Transformation.
  • In the “SQL Ports” tab, enter the sql question as ~Query_Port~. The tilt signifies a variable substitution for the queries.
  • As we don’t want any output, simply join the SQLError port to the goal.
  • Now create workflow and run the workflow.

SQL Transformation Example Using Partial Dynamic question

Q3) In the instance 2, you possibly can see the delete statements are comparable besides Athe desk title. Now we are going to move solely the desk title to the sql transformation. The supply desk incorporates the under information.

Table Name: Del_Tab
Tab_Names
----------
gross sales
merchandise

Solution:

Create the enter port in the sql transformation as Table_Name and enter the under question in the SQL Query window.

Delete FROM ~Table_Name WHERE Product="LG"

Recommended Reading


SQL transformation in script mode.
Joiner Transformation in Informatica
Informatica Scenario Based Questions
Aggregator Transformation in Informatica
Source Qualifier Transformation in Informatica

If you want this text, then please share it or click on on the google +1 button.

Leave a Reply