Transformations

Back Next

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’

Rename Saved Search

Table of Contents

Transformations

Transformations

Static SQL queries

Static SQL queries

Example