...
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 |
...
you |
...
SQL |
...
statements |
...
do |
...
not |
...
change |
...
a |
...
lot, |
...
check |
...
this |
...
option |
...
to |
...
reduce |
...
the |
...
number |
...
of |
...
phisical |
...
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). |
...
|
...
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.
...