Hitachi Vantara Pentaho Community Wiki
Child pages
  • Dynamic SQL row
Skip to end of metadata
Go to start of metadata

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

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.

  • No labels

1 Comment

  1. The "Template SQL (to retrieve Meta data)" section of this step is a bit confusing.

    As per the PDI Guru, Samatar:

    PDI will only know what the fields are from your dynamic sql step at runtime, howver PDI needs to know how to add these fields to the stream etc. So you basically add a SQL stmt without any of the dynamic stuff so that PDI can fire this off to the db and get back a type or meta data for the fields it will be getting at runtime when the dynamic parts of the sql stmt are "bound" and issued against the db.

    So if I was wanting to read from a text file input step and use that information in a SQL stmt.

    I might have a text file input followed by a javascript that actually assembles the stmt .. and goes out in a var named run_this_stmt.

    After the JS step... and lets say during runtime... run_this_stmt may have "SELECT name, age, breed FROM table_dogs WHERE breed = 'husky' .

    So the text file input step has a list of dog breeds.

    The JS step "makes" the SQL stmt dynamic by taking a breed from the text file input and appending it to the hard-coded string that contains most of the SQL stmt.

    ex.

    var run_this_stmt = "SELECT name, age, breed FROM table_dogs WHERE breed = '";

    run_this_stmt = run_this_stmt + breed + "'";

    Ok so "run_this_stmt" is what the JS outputs.

    In the Dynamic SQL Step you would make sure you enter in the corect db connection details and then the SQL field name would be "run_this_stmt" and within the Template SQL pane you would enter the sql stmt minus the dynamic part like:

    SELECT name, age, breed FROM table_dogs

    Hope that is clear as mud!

    Thx

    Kent