1. SQL Transformation
SQL Transformation in Informatica Cloud is used to call a Stored Procedure or Function, or execute SQL queries midstream in a mapping pipeline.
In our previous article, we have discussed how to call stored procedures in a mapping using SQL transformation. In this article let us understand how to process SQL queries using SQL transformation in a mapping.
SQL Transformation in Informatica Cloud
2. Query Mode in SQL Transformation
A SQL Transformation can be used to process a SQL query either using a “Saved Query” or a User “Entered Query”. The SQL transformation can return multiple output rows of the configured SQL query for each input row.
The following type of SQL queries can be created in SQL transformation.
- Static SQL Query
- Dynamic SQL Query
3. Static SQL Queries in SQL Transformation
A Static SQL Query runs the same query statement for each input row in SQL transformation. But the data of the query can be changed for each input row using the parameter binding in the SQL editor.
Problem Statement:
Below is the problem statement used for the demonstration of Static SQL Queries.
- The DEPARTMENTS table is considered as source.
- The salary details of employees are present in EMPLOYEES table.
- Both the tables can be joined using the field DEPARTMENT_ID.
- The requirement is to calculate the AVG salary of employees in each department.
Follow below steps to fulfil the above stated requirement using Static SQL Query in a SQL Transformation.
1. In the source transformation, select the DEPARTMENTS table as the source object.
2. Drag and drop the SQL transformation on to mapping canvas.
3. In the Properties panel SQL transformation, under SQL tab
- Select the database Connection where the query should be executed.
- Select the SQL Type as SQL Query.
- Select the Query Type as Entered Query.
- Enter below query which provides department ID and its corresponding average salary in the query editor and Validate it.
Note that the input field DEPARTMENT_ID is used as a binding parameter in the query. The field name is enclosed in question marks ? to use as a binding parameter in SQL Query.
4. In the Output Fields tab, under Query Fields, an output field must be configured for each column in the SELECT statement.
Two fields of type integer are created to capture DEPARTMENT_ID and AVG_SALARY field values as shown below.
When the number of output fields is less than the number of columns in the SELECT clause, Data Integration generates a row error. The output fields must be in the same order as the columns in the SELECT statement.
5. Additionally configure Pass-Through Fields if any fields from source should be taken towards target.
The below image shows that the field DEPARTMENT_NAME coming from source table is selected as a pass-through field.
6. Pass the data from SQL transformation to the Target transformation.
7. Configure the target, Validate and Save the mapping.
The below image shows the output of the mapping with AVG salary value against each department.
4. Dynamic SQL Queries in SQL Transformation
A Dynamic SQL query can execute different query statements for each input row. The SQL query executed for each input row is changed using the string variables in the query which link to the input fields passed to the SQL transformation.
To configure a string variable in the query, identify an input field by name in the query and enclose the name in tilde characters ( ~). The query changes based on the value of the data in the field. The input field that contains the query variable must be a string data type.
A part of the query or entire query can be passed as a string variable to the SQL transformation.
4.1. Full Dynamic Query
The entire SQL query can be substituted with query statements from source.
Problem Statement:
Execute a list of DDL/DML SQL statements using SQL transformation in a mapping
Follow below steps to execute Full Dynamic SQL statements using SQL transformation.
1. Create a flat file with list of all the queries to be executed with header field as QUERY_FIELD.
QUERY_FIELD
create table currency_code(country varchar2(3), currency varchar2(3));
Insert into currency_code values('USA','USD');
Insert into currency_code values('IND','INR');
select count(*) from currency_code;2. Select the file with SQL statements in Source transformation. Modify the formatting options to change the delimiter as semicolon (;)
3. Drag and drop the SQL transformation on to mapping canvas.
4. In the Properties panel SQL transformation, under SQL tab
- Select the database Connection where the query should be executed.
- Select the SQL Type as SQL Query.
- Select the Query Type as Entered Query.
- In the query editor, select the input filed QUERY_FIELD enclosed in ~ characters as shown below.
5. In the Output Fields tab, under Query Fields, output fields must be configured if there are any SELECT statements.
Since in the input SQL queries, we have one SQL statement, an output field to capture the record count is created.
6. Additionally configure Pass-Through Fields to take the SQL queries towards target.
7. Pass the data from SQL transformation to the Target transformation.
8. Configure the target, Validate and Save the mapping.
The below image shows the output of SQL transformation with Dynamic SQL queries.
The SQL transformation returns a row with NULL data in the output fields for the statements which do not return database data.
4.2. Partial Dynamic Query
A portion of SQL query (like table names) can be substituted with input fields from source.
Problem Statement:
Calculate the record count of tables in a database using SQL transformation in a mapping. The table names are passed as source to the mapping.
Follow below steps to get the record count of tables by passing table name as string variable to the Partial Dynamic SQL query using SQL transformation.
1. Create a flat file with list of all the table names with header field as TABLE_NAME.
TABLE_NAME
employees
departments
locations2. Select the file with table names in Source transformation.
3. Drag and drop the SQL transformation on to mapping canvas.
4. In the Properties panel SQL transformation, under SQL tab
- Select the database Connection where the query should be executed.
- Select the SQL Type as SQL Query.
- Select the Query Type as Entered Query.
- Enter below query in the query editor which provides record count of tables passed from source and Validate it.
Note that the input field TABLE_NAME is enclosed in ~ to be used as a string variable in the query.
5. In the Output Fields tab, under Query Fields, create an output field RECORD_COUNT to capture the record count.
6. Select the TABLE_NAME field under Pass-Through Fields to take the table names passed from source towards target.
7. Pass the data from SQL transformation to the Target transformation.
8. Configure the target, Validate and Save the mapping.
The below image shows the output of the mapping with table names and their record count value.
5. Passive Mode in SQL Transformation
By default when a SQL transformation is created in a mapping, it will be in Active mode i.e. when a SELECT query returns more than one row, all the rows will be returned by the SQL transformation.
The SQL transformation can also be configured to be Passive from Advanced properties of the transformation. When you configure the transformation as a passive transformation and a SELECT query returns more than one row,
- Data Integration returns the first row and an error to the SQLError field.
- The error states that the SQL transformation generated multiple rows.
6. Supported statements in SQL Transformation
The following table lists the statements that are supported in an SQL query in the SQL transformation.
| Statement Type | Supported Statements |
| DDL | ALTER, COMMENT, CREATE, DROP, RENAME, TRUNCATE |
| DML | CALL, DELETE, EXPLAIN PLAN, INSERT, LOCK TABLE, MERGE, SELECT, UPDATE |
| DCL | GRANT, REVOKE, COMMIT, ROLLBACK |
SQL Transformation in Informatica Cloud
Subscribe to our Newsletter !!
Related Articles:
Shared Sequences are reusable sequences that can be used with multiple Sequence Generator transformations in Informatica Cloud.
IICS REST V2 Connector helps to interact with Web Service applications built on REST architecture. You can use REST V2 Connector in a Source, Target or midstream in a WebServices transformation.
SQL Transformation in Informatica Cloud is used to call a Stored Procedure or Function, or execute SQL queries midstream in a mapping pipeline.
