Description
The Dynamic SQL row step allows you to execute a SQL statement that is defined in a database field. The lookup values are added as new fields onto the stream
Options
The following table describes the available options for configuring the Dynamic SQL row step
Option |
Description |
|---|---|
Step name |
Name of the step; This name has to be unique in a single transformation |
Connection |
Select a database connection for the lookup |
SQL field name |
Specify the field that contains the SQL to execute |
Number of rows to return |
Specify the number of records to return. 0 means, return all rows |
Outer Join |
- false: don't return rows where nothing is found - true: at least return one source row, the rest is NULL |
Replace variables |
In case you want to use variables in the SQL, e.g. ${table_name}, this option needs to be checked. |
Query only on parameters change |
If your SQL statements do not change a lot, check this option to reduce the number of physical database queries. |
Template SQL |
In PDI meta and data are separate so you have to specify the meta part in template SQL (field name and type). |
It can be :
SELECT
1 AS MyIntegerField
'a string' AS MyStringfield
cast('2009-01-01 00:00:00' as date) AS MyDate
(!) *Important*: If your sql statement did not change a lot and in order to not query the database for each row, check option "Query only on parameter" (and make sure you have sorted the stream on SQL fieldname), PDI query again only if the field content has changed (current value will be compared with previous value) otherwise it will return value from cache.