8.1.1.4 Analyzing Join Counts

A join identifies the relationship between two tables by common fields. Checking for join counts determines the referential integrity between two or more tables.

Determine join counts as follows:

• Simple join between two or more tables.
• Simple join between two or more tables with filter restriction.
• Join count of distinct values for specific column.

The join count analysis is structured within the following elements:

<ANALYSIS>
    <JOINS>
        <!-- analysis for referential integrity here -->
    </JOINS>
</ANALYSIS>

Simple Join

A join is set within the <JOIN> element. To retrieve the join count between two or more tables, the joins are identified within the <MULTIJOIN> element. Within this <MULTIJOIN> element, multiple <JOIN> elements can be set. Because a join retrieves the join count between two or more tables, <LEFT> and <RIGHT> elements are used to indicate the tables. The <LEFT> element identifies the first table and its field using the table and column attributes. The table and column attributes for the <RIGHT> element identify the second table and field. The structure for a simple join count analysis is:

<MULTIJOIN>
<!-- May contain more than one JOIN element -->
    <JOIN>
        <LEFT table="table name" column="column" />
        <RIGHT table="table name" column="column" />
    </JOIN>
</MULTIJOIN>

The following XML code provides an example:

<ANALYSIS>
<JOINS>
<MULTIJOIN>
<JOIN>
<LEFT table="ACCT" column="ACCT_INTRL_ID" />
<RIGHT table="CUST_ACCT" column="ACCT_INTRL_ID" />
</JOIN>
</MULTIJOIN>
<MULTIJOIN>
<JOIN>
<LEFT table="ACCT" column="ACCT_INTRL_ID" />
<RIGHT table="CUST_ACCT" column="ACCT_INTRL_ID" />
</JOIN>
<JOIN>
<LEFT table="CUST" column="CUST_INTRL_ID" />
<RIGHT table="CUST_ACCT" column="CUST_INTRL_ID" />
</JOIN>
</MULTIJOIN>
</JOINS>
</ANALYSIS>

This XML code executes the following queries:

select count(1)
from ACCT a, CUST_ACCT b
where a.ACCT_INTRL_ID=b.ACCT_INTRL_ID

select count(1)
from ACCT a, CUST_ACCT b, CUST c 
where a.ACCT_INTRL_ID=b.ACCT_INTRL_ID 
and c.CUST_INTRL_ID=b.CUST_INTRL_ID

Simple Join with Filter Restriction

Adding a filter to the joins determines the join count between tables with a restriction. A filter uses the table, field, operator, and value attributes to set the restriction. The operator is limited to the XML code operators.

The structure is organized in the same manner as a Simple Join with an added <FILTER> element. The following code illustrates the structure:

<MULTIJOIN>
<JOIN>
<LEFT table="table name" column="column" />
<RIGHT table="table name" column="column" />
</JOIN>
<!-- Optional. May contain one or more filters. -->
<FILTER table="table name" column="column" operator=
"operator" value="filter value" />
</MULTIJOIN>

The <FILTER> element is optional in the join analysis. Multiple filters can be applied to a join. The AND operator is appended to each filter condition upon creation of the query. The following XML code illustrates the use of a filter with a simple join analysis:

<ANALYSIS>
<JOINS>
<MULTIJOIN>
<JOIN>
<LEFT table="ACCT" column="ACCT_INTRL_ID" />
<RIGHT table="CUST_ACCT" column="ACCT_INTRL_ID" />
</JOIN>
<FILTER table="ACCT" column="DATA_DUMP_DT"
operator="GTE" value="01-NOV-2006" />
<FILTER table="ACCT" column="DATA_DUMP_DT"
operator="LTE" value="05-NOV-2006" />
</MULTIJOIN>
</JOINS>
</ANALYSIS>

This code executes the following query:

select count(1) from ACCT a, CUST_ACCT b
where a.ACCT_INTRL_ID=b.ACCT_INTRL_ID
and a.DATA_DUMP_DT>='01-NOV-2006' and a.DATA_DUMP_DT<='05-NOV-2006'

To filter for values that are null or not null, set the operator to EMPTY and the value to IS NULL or IS NOT NULL, respectively.

Join Count by Distinct Column

To determine a join count of the number of distinct values for a specified column within the joined tables, include the <DISTINCT_COUNT> element as content to the <MULTIJOIN> element. The targeted table and its column are set to the table and column attributes, respectively. The following sample demonstrates integration of the <DISTINCT_COUNT> element in the analysis:

<MULTIJOIN>
<JOIN>
<LEFT table="table name" column="column" />
<RIGHT table="table name" column="column" />
</JOIN>
<!-- Optional. Can only have one DISTINCT_COUNT within
the MULTIJOIN element. -->
<DISTINCT_COUNT table="table name" column="column" />
</MULTIJOIN>

Note:

The <DISTINCT_COUNT> element is optional in the join analysis

The following XML sample code illustrates use of the <DISTINCT_COUNT> element:

<ANALYSIS>
<JOINS>
<MULTIJOIN>
<JOIN>
<LEFT table="ACCT" column="ACCT_INTRL_ID" />
<RIGHT table="CUST_ACCT" column="ACCT_INTRL_ID" />
</JOIN>
<FILTER table="ACCT" column="DATA_DUMP_DT" operator=
"EQ" value="02-NOV-2006" />
<DISTINCT_COUNT table="ACCT" column="ACCT_TYPE_CD" />
</MULTIJOIN>
</JOINS>
</ANALYSIS>

This sample code executes the following query:

select count(DISTINCT a.ACCT_TYPE_CD)
from ACCT a, CUST_ACCT b
where a.ACCT_INTRL_ID=b.ACCT_INTRL_ID and a.DATA_DUMP_DT='02-NOV-2006'