Not available in Community Designer

Join key is a sequence of field names from a master data source separated from each other by a semicolon, colon, or pipe. You can define the key in the Edit key wizard.

Order of these field names must correspond to the order of the key fields from the database table (and their data types). The slave part of Join key must be defined in the SQL query attribute.

One of the query attributes must contain the expression of the following form: ... where field_K=? and field_L=?.

$first_name;$last_name

... where fname=? and lname=?

The transform in DBJoin lets you define a transformation that sends records to the first output port. The unjoined master records sent to the second output port cannot be modified within the DBJoin transformation.

Input records contain one field customerId with values:

1
35535
255

The customers database table has the following structure and data:

customer_id | customer_name | customer_surname
 1          | John          | Doe
 3          | Anna          | Smith
10          | Jane          | Brown

Join incoming records with data from database on customerId.

Solution

Create input metadata with one field customerId (integer) and output metadata with the customerId (integer), customerName (string), customerSurname (string) fields.

Set the Join key and SQL query attributes of DBJoin.

Only records with customerId found in the database are sent to output.

Input records contain product code and date of order.

productCode | orderDate
1           | 2015-10-20
2           | 2015-10-30
1           | 2015-11-06
	

Price of a particular product on particular date can be found in the database.

productCode | dateFrom   | dateTo     | price 
1           | 2014-01-01 | 2015-10-31 |  10.99
1           | 2015-11-01 | 2099-12-31 |  14.99
2           | 2015-07-01 | 2099-12-31 | 109.99

Match the price to particular product orders.

Solution

Create input metadata (productCode, orderDate), output metadata (productCode, orderDate, price) and metadata for DBJoin ( productCode, price).

Set attributes

//#CTL2

function integer transform() {
	$out.0.* = $in.0.*;
	$out.0.* = $in.1.*;

	return ALL;
}

Since CloverETL 4.2.0-M1, DBJoin propagates metadata between first input port and second output port.