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

Technote (FAQ)


Question

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)

Answer

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.

Rate this page:

(0 users)Average rating

Add comments

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

Translate my page

Machine Translation

Content navigation