8.1.1.2 Analyzing Distinct Values for Fields of Interest

Identifying the table and one or more column combinations of interest provides a combination of distinct values and number of occurrences in the table.

The following code illustrates the required structure of this analysis within the following elements:

<ANALYSIS>
<TABLES>
<analysis for distinct values occurs here>
</TABLES>
</ANALYSIS>

• The name attribute of the <TABLE> element identifies the table against which this analysis is applied.
• The <VALUES> element identifies targeted columns.
• The field attribute of the <COLUMN> element sets each database column.

Application of filters to an analysis is possible if the <CONSTRAINT> element identifies the filter. The following code illustrates the structure for using a filter:

<TABLE name="table name">
<!-- get distinct value for one column -->
<VALUES>
<COLUMN field="column name"/>
<!-- Constraint feature is optional.
May contain one or more constraints. -->
<CONSTRAINT field="column name" operator="operator"
value="filter value" />
</VALUES>
<!-- get distinct value for many columns -->
<VALUES>
<COLUMN field="column name"/>
<COLUMN field="column name"/>
<!-- Constraint feature is optional.
May contain one or more constraints. -->
<CONSTRAINT field="column name"
operator="operator"value="filter value" />
</VALUES>
</TABLE>

The following XML code illustrates use of a filter:

<ANALYSIS>
<TABLES>
<TABLE name="ACCT">
<VALUES>
<COLUMN field="ACCT_TYPE1_CD"/>
<COLUMN field="ACCT_TYPE2_CD"/>
</VALUES>
</TABLE>
<TABLE name="CUST">
<VALUES>
<COLUMN field="CUST_TYPE_CD"/>
<CONSTRAINT field="DATA_DUMP_DT" operator="EQ"
value="15-NOV-2006" />
</VALUES>
</TABLE>
</TABLES>
<ANALYSIS>

This XML code executes the following queries:

select ACCT_TYPE1_CD, ACCT_TYPE2_CD, count(1)
from ACCT
group by ACCT_TYPE1_CD, ACCT_TYPE2_CD
select CUST_TYPE_CD, count(1)
from CUST
where DATA_DUMP_DT='15-NOV-2006'
group by CUST_TYPE_CD