Dynamic SQL row


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


The following table describes the available options for configuring the Dynamic SQL row step



Step name

Name of the step; This name has to be unique in a single transformation


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).
I mean any statement that returns the same row structure.

It can be :

   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.