SQL Transformation Query
Create an SQL query in the SQL Editor to retrieve rows from a database or to update the database.
To create a query, type the query statement in the SQL Editor in the SQL view. The SQL Editor provides a list of the transformation ports that you can reference in the query. You can double-click a port name to add it as a query parameter.
When you create a query, the SQL Editor validates the port names in the query. It also verifies that the ports you use for string substitution are string datatypes. The SQL Editor does not validate the syntax of the SQL query.
You can use constants in the SQL query. Enclose each string in a single quote (').
The following figure shows a sample SQL query:
You can create a static SQL query. The query statement does not change, but you can include parameters to change values. The Data Integration Service runs the query for each input row.
Define the SQL Query
Define an SQL query that runs the same query statements for each input row. You can change the query columns or table based on input port values in the row. You can also change the values in the WHERE clause based on input port values.
To change the data values in the WHERE clause for each input row, configure parameter binding.
To change the query columns or to change the table based on input port values, use string substitution.
Parameter Binding
To change the data in the query, configure the query parameters and bind the query parameters to input ports in the transformation. When you bind a parameter to an input port, identify the port by name in the query. The SQL Editor encloses the port name in question marks (?). The query data changes based on the value of the data in the port.
The following 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
The following SQL query has query parameters that bind to the Employee_ID and Dept input ports of an SQL transformation:
SELECT Name, Address FROM Employees WHERE Employee_Num =?Employee_ID? and Dept = ?Dept?
The source might have the following rows:
Employee_ID | Dept |
|---|
100 | Products |
123 | HR |
130 | Accounting |
The Data Integration Service 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’
String Substitution
Use string variables to replace components of query statements. For example, you can use the string variable to replace the table name in a query. Or, you can substitute the column names in a SELECT statement.
To substitute the table name, configure an input port to receive the table name from each input row. In the SQL Editor, select the port from the String Substitution list of ports. The Developer tool identifies the input port by name in the query and encloses the name with the tilde (~).
The following query contains a string variable, ~Table_Port~:
SELECT Emp_ID, Address from ~Table_Port~ where Dept = ‘HR’
The source might pass the following values to the Table_Port column:
Table_Port |
|---|
Employees_USA |
Employees_England |
Employees_Australia |
The Data Integration Service replaces the ~Table_Port~ variable with the table name value in the input port:
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’
