Description
The Database join Join step allows you to run a query against a database using data obtained from previous steps. The parameters for this query can be are specified as follows:
- as The data grid in the step properties dialog. This allows you to select the data coming in from the source hop.
- As question marks (?) in the SQL query. as fields in When the step runs, these will be replaced with data coming in from the fields defined from the data grid.
...
- The question marks will be replaced in the same order
...
- as defined in the
...
- data grid.
For example, Database join Join allows you to run queries looking up the oldest person who bought a specific product as shown below:
| Code Block |
|---|
SELECT customernr FROM product_orders, customer WHERE orders.customernr = customer.customernr AND orders.productnr = ? ORDER BY customer.date_of_birth |
You then need to specify the productnr as a parameter and you will get the customernr included in the resultThe grid is the defined as follows: 
When the step runs, the (?) placeholder will be replaced with the incoming productnr field value from the source hop. To define and use multiple parameters, list the fields in order you want them to be replaced in the SQL statement.
Options
The following table desribes the options for the Database Join step:
Option | Description |
|---|---|
Step name | Name of the step; This name has to be unique in a single transformation |
Connection | The database connection to use for the query. |
SQL | SQL query to launch towards form the databasejoin; use question marks as parameter placeholders |
Number of rows to return | Zero (0) returns all rows; any other number limits the number of rows returned. |
Outer join? | Enable to always return a result, even if the query did not return a result |
Parameters table | Specify the fieldns fields containing parameters and the . The parameter type is required. |