Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

facebook Twitter
Print Bookmark Alert me when this article is updated


FAQ: Can a PowerCenter mapping be configured to perform a non-equi join on heterogeneous sources?
Problem Description

How to join on two tables master and detail where the join condition is not an equality test like the following:


The Joiner Transformation only appears to allow an equality for the join condition.​

Non-equi joins are not supported in the Joiner Transformation.

You can use one of the following alternatives:

  • SQL transformation
  • Lookup transformation (9.x and later releases)
  • Multiple mappings

SQL transformation

You can also use a SQL transformation in a mapping to perform a heterogeneous non-equi join.
Refer to article 106787 for a sample mapping: HOW TO: Sample PowerCenter mapping that joins heterogeneous relational sources with a non-equi join condition using a SQL transformation 

Lookup transformation (9.x)

Effective in 9.x a lookup transformation can be configured to emulate a non-equi join on heterogeneous sources.
Refer to article 115337 for details:
HOW TO: Perform a non-equi join on two heterogeneous sources using a Lookup transformation in a PowerCenter mapping

Multiple Mappings

You can stage the data from one of the tables to a table in the same database as the other source then do the non-equi join in the Source Qualifier.

More Information

A feature request (CR 84967) has been created to add a non-equi join option for Joiner transformations in a future release.

Applies To
Product: PowerCenter
Problem Type:
User Type: Administrator
Project Phase:
Product Version: PowerCenter
Operating System:
Other Software:


CR 84967
CR 38752
CR 61677
CR 64379

Last Modified Date:7/25/2017 1:23 PMID:11086
People who viewed this also viewed


Did this KB document help you?

What can we do to improve this information (2000 or fewer characters)