IBM Support What's New?

In IBM InfoSphere Information Analyzer Rules, How do I join a Table to Itself?

Technote (FAQ)


Is it possible to join a table to itself as per the following requirement:

tableA has a key of key1 (non unique key); if the dup_flag on tableA is 'Y' then there is another record on tableA with key of key1 with dup_flag on tableA equal to ‘D’

The pseudo code to represent this is:
IF tableA.dup_flag = ‘Y’ ( vTableA.dup_flag = ‘D’)
WHERE (tableA.key1 = vTableA. key1)


Perform the following:

Here is the data:

Create a 'virtual table' from the base table, tableA.

Refer to the following for creating Virtual tables (note it is the same procedure for all versions):
Creating a virtual table

When you create the virtual table you can provide a 'filter' or WHERE clause through the interface.
Set it to only select items where dup_flag is ‘D’

Here is the Rule Definition:
If tabDup_flag = 'Y' then vTabDup_flag = ‘D’

The virtual table created above is now available as a data source (To be bound to) in Data Rule

In the Data Rule, Make sure the binding is correct

Bind as needed.

Setup the output to include Additional Items. This allows us to setup the JOIN condition.
Make sure Output Records is set to All Records for this test.

Join the two tables on the key1 column.

After running the rule and doing a 'View Output', this is the initial screen

This is correct per our data.

Document information

More support for: InfoSphere Information Analyzer

Software version: 8.1, 8.5, 8.7

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Reference #: 1573895

Modified date: 2012-10-08