IBM Support

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

Question & Answer


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.

[{"Product":{"code":"SSZJLG","label":"InfoSphere Information Analyzer"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"--","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"8.7;8.5;8.1","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
16 June 2018

UID

swg21573895