You can configure the SQL transformation to process a saved query or a user-entered query that runs against a Microsoft SQL Server or Oracle database. When you configure the SQL transformation to process a query, you create an active transformation. The transformation can return multiple rows for each input row.
When you enter a query, you can format the SQL and validate the syntax. Alternatively, you can create a string parameter to define the query in the mapping task.
You can create the following types of SQL queries:
Static SQL query
The query statement does not change, but you can use query parameters to change the data. Data Integration prepares the SQL query once and runs the query for all input rows.
Dynamic SQL query
You can change the query statements and the data. Data Integration prepares an SQL query for each input row.
You can optimize performance by creating static queries.
Static SQL queries
Create a static SQL query when you need to run the same query statements for each input row, but you want to change the data in the query for each input row. When you create a static SQL query, you use parameter binding in the SQL editor to define parameters for query data.
To change the data in the query, you configure query parameters and bind them to input fields in the transformation. When you bind a parameter to an input field, you identify the field by name in the query. Enclose the field name in question marks (?). The query data changes based on the value of the data in the input field.
For example, the following static queries use parameter binding:
DELETE FROM Employee WHERE Dept = ?Dept? INSERT INTO Employee(Employee_ID, Dept) VALUES (?Employee_ID?, ?Dept?) UPDATE Employee SET Dept = ?Dept? WHERE Employee_ID > 100
Example
The following static SQL query uses query parameters that bind to the Employee_ID and Dept input fields of an SQL transformation:
SELECT Name, Address FROM Employees WHERE Employee_Num = ?Employee_ID? and Dept = ?Dept?
The source has the following rows:
Employee_ID
Dept
100
Products
123
HR
130
Accounting
Data Integration generates the following query statements from the rows:
SELECT Name, Address FROM Employees WHERE Employee_ID = ‘100’ and DEPT = ‘Products’ SELECT Name, Address FROM Employees WHERE Employee_ID = ‘123’ and DEPT = ‘HR’ SELECT Name, Address FROM Employees WHERE Employee_ID = ‘130’ and DEPT = ‘Accounting’
Selecting multiple database rows
When the SQL query contains a SELECT statement, the transformation returns one row for each database row it retrieves. You must configure an output field for each column in the SELECT statement. The output fields must be in the same order as the columns in the SELECT statement.
When you configure output fields for database columns, you must configure the data type of each database column that you select. Select a native data type from the list. When you select the native data type, Data Integration configures the transformation data type for you.
The native data type in the transformation must match the database column data type. Data Integration matches the column data type in the database with the native database type in the transformation at run time. If the data types do not match, Data Integration generates a row error.
Dynamic SQL queries
A dynamic SQL query can execute different query statements for each input row. When you create a dynamic SQL query, you use string substitution to define string variables in the query and link them to input fields in the transformation.
To change a query statement, configure a string variable in the query for the portion of the query that you want to change. To configure the string variable, 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 transformation input field that contains the query variable must be a string data type. You can use string substitution to change the query statement and the query data.
When you create a dynamic SQL query, Data Integration prepares a query for each input row. You can pass the following types of dynamic queries in an input field:
Full query
You can substitute the entire SQL query with query statements from source data.
Partial query
You can substitute a portion of the query statement, such as the table name.
Passing the full query
You can pass the full SQL query through an input field in the transformation. To pass the full query, create a query in the SQL editor that consists of one string variable to represent the full query: ~Query_Field~
To pass the full query, configure the source to pass the full query in an output field. Then, configure the SQL transformation to receive the query in the Query_Field input field.
The following image shows the transformation configuration:
Data Integration replaces the ~Query_Field~ variable in the dynamic query with the SQL statements from the source. It prepares the query and sends it to the database to process. The database executes the query. The SQL transformation returns database errors to the SQLError output field.
When you pass the full query, you can pass more than one query statement for each input row. For example, the source might contain the following rows:
DELETE FROM Person WHERE LastName = ‘Jones’; INSERT INTO Person (LastName, Address) VALUES ('Smith', '38 Summit Drive') DELETE FROM Person WHERE LastName = ‘Jones’; INSERT INTO Person (LastName, Address) VALUES ('Smith', '38 Summit Drive') DELETE FROM Person WHERE LastName = ‘Russell’;
You can pass any type of query in the source data. When you configure SELECT statements in the query, you must configure output fields for the database columns that you retrieve from the database. When you mix SELECT statements and other types of queries, the output fields that represent database columns contain null values when no database columns are retrieved.
Substituting the table name in a string
You can use a partial query to substitute the table name. To substitute the table name, configure an input field to receive the table name from each input row. Identify the input field by name in the query and enclose the name with tilde characters (~).
For example, the following dynamic query contains a string variable, ~Table_Field~:
SELECT Emp_ID, Address from ~Table_Field~ where Dept = ‘HR’
The source might pass the following values to the Table_Field column:
Table_Field
Employees_USA
Employees_England
Employees_Australia
Data Integration replaces the ~Table_Field~ variable with the table name in the input field:
SELECT Emp_ID, Address from Employees_USA where Dept = ‘HR’ SELECT Emp_ID, Address from Employees_England where Dept = ‘HR’ SELECT Emp_ID, Address from Employees_Australia where Dept = ‘HR’
Passive mode configuration
When you create an SQL transformation, you can configure it to run in passive mode instead of active mode. A passive transformation does not change the number of rows that pass through it. It maintains transaction boundaries and row types.
If you configure the SQL transformation to process a query, you can configure passive mode when you create the transformation. Configure passive mode in the transformation advanced properties.
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.
If the SQL query has multiple SQL statements, Data Integration executes all statements but returns data for the first SQL statement only. The SQL transformation returns one row. The SQLError field contains the errors from all SQL statements. When multiple errors occur, they are separated by semicolons (;) in the SQLError field.
SQL statements that you can use in queries
You can use certain data definition, data manipulation, data language control, and transaction control statements with the SQL transformation.
The following table lists the statements that you can use in an SQL query in the SQL transformation:
Statement Type
Statement
Description
Data Definition
ALTER
Modifies the structure of the database.
Data Definition
COMMENT
Adds comments to the data dictionary.
Data Definition
CREATE
Creates a database, table, or index.
Data Definition
DROP
Deletes an index, table, or database.
Data Definition
RENAME
Renames a database object.
Data Definition
TRUNCATE
Removes all rows from a table.
Data Manipulation
CALL
Calls a PL/SQL or Java subprogram.
Data Manipulation
DELETE
Deletes rows from a table.
Data Manipulation
EXPLAIN PLAN
Writes the access plan for a statement into the database Explain tables.
Data Manipulation
INSERT
Inserts rows into a table.
Data Manipulation
LOCK TABLE
Prevents concurrent application processes from using or changing a table.
Data Manipulation
MERGE
Updates a table with source data.
Data Manipulation
SELECT
Retrieves data from the database.
Data Manipulation
UPDATE
Updates the values of rows of a table.
Data Control Language
GRANT
Grants privileges to a database user.
Data Control Language
REVOKE
Removes access privileges for a database user.
Transaction Control
COMMIT
Saves a unit of work and performs the database changes for that unit of work.
Transaction Control
ROLLBACK
Reverses changes to the database since the last COMMIT.
Rules and guidelines for query processing
Use the following rules and guidelines when you configure the SQL transformation to process a query:
•The number and the order of the output fields must match the number and order of the fields in the query SELECT clause.
•The native data type of an output field in the transformation must match the data type of the corresponding column in the database. Data Integration generates a row error when the data types do not match.
•When the SQL query contains an INSERT, UPDATE, or DELETE clause, the transformation returns data to the SQLError field, the pass-through fields, and the NumRowsAffected field when it is enabled. If you add output fields, the fields receive NULL data values.
•When the SQL query contains a SELECT statement and the transformation has a pass-through field, the transformation returns data to the pass-through field whether or not the query returns database data. The SQL transformation returns a row with NULL data in the output fields.
•When the number of output fields is more than the number of columns in the SELECT clause, the extra fields receive a NULL value.
•When the number of output fields is less than the number of columns in the SELECT clause, Data Integration generates a row error.
•You can use string substitution instead of parameter binding in a query. However, the input fields must be string data types.
