Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

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:

DATE_MASTER >= DATE_DETAIL

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

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
Database:
Operating System:
Other Software:

Reference

CR 84967
CR 38752
CR 61677
CR 64379

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

Feedback

Did this KB document help you?



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