Database Join

Description

The Database Join transform allows you to run a query against a database using data obtained from previous transforms. The parameters for this query are specified as follows:

The data grid in the transform properties dialog. This allows you to select the data coming in from the source hop. As question marks (?) in the SQL query. When the transform 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 allows you to run queries looking up the oldest person who bought a specific product as shown below:

the Database Join transform gives you more flexibility than the standard Database Lookup transform. Keep in mind that your query determines the performance of this transform.
  1. SELECT      customernr
  2. FROM        product_orders, customer
  3. WHERE       orders.customernr = customer.customernr
  4. AND         orders.productnr = ?
  5. ORDER BY    customer.date_of_birth

The grid is then defined as follows:

databasejoin grid

When the transform runs, the (?) placeholder defined in the SQL query 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

OptionDescription

Transform name

Name of the transform; This name has to be unique in a single pipeline

Connection

The database connection to use for the query.

SQL

SQL query to form the join; 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 fields containing parameters. The parameter type is required.