In IBM InfoSphere Information Analyzer Rules, How do I join a Table to Itself?
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.
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: 08 October 2012