Transformations in Sql

Sql transformation is to perform the sql queries in the midstream of pipeline. We can perform the operations like insert, update, delete and retrieve rows from the database at run time by sql transformation technique.

In the Sql editor all SQL transformation processes like external SQL scripts or SQL queries can be performed. Here users can also pass the database connection information to the SQL transformation as an input data at run time. Get in depth knowledge of sql through sql online course

SQL Transformation Queries

Query Mode: The SQL transformation executes a query that defined in the query editor. You can pass parameters to the query to define dynamic queries. The SQL transformation can output multiple rows when the query has a select statement. In query mode, the SQL transformation acts as an active transformation.

Static SQL query: The SQL query statement does not change, however you can pass parameters to the sql query. The integration service runs the query once and runs the same query for all the input rows.

Dynamic SQL query: The SQL query statement and the data can change. The integration service prepares the query for each input row and then runs the query. prepare over important sql and pl sql interview questions

Creating an SQL Transformation

Get Veeereshkumar’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

You can create an SQL transformation in the Transformation Developer or the Mapping Designer.

• To create an SQL transformation:
• Click Transformation > Create.
• Select the SQL transformation.
• Enter a name for the transformation
• The naming convention for an SQL transformation is SQ Transformation Name.
• Enter a description for the transformation and click Create.
• Enter a name for the transformation.
• Query mode. Configure an active transformation that executes dynamic SQL queries.
• Script mode. Configure a passive transformation that executes external SQL scripts.
• Configure the SQL transformation connection options:
• To configure the SQL transformation to run in passive mode, select SQL Transformation Will Run in Passive Mode.
• Click OK to configure the transformation.
• The Designer creates default ports in the transformation based on the options that you choose. You cannot change the configuration except for the database type.
• Click the Ports tab to add ports to the transformation. Add pass-through ports after database ports.
• Click OK to configure the transformation.
• The Designer creates default ports in the transformation based on the options that you choose. You cannot change the configuration except for the database type.
• Click the Ports tab to add ports to the transformation. Add pass-through ports after database ports.

To get in depth knowledge on sql and pl sql. master yous skills on sql and pl sql through sql course

Data Types in Sql/Plsql