8.1.1.3 Analyzing Null and Padded Space Count

Null and padded space count analysis provides the number of occurrences for null values and padded spaces for a particular field in a table.

You perform this analysis by identifying the table and one or more columns of interest. The null analysis feature has the following limitations:

• The feature is optional.
• The field identified for the specified table can be analyzed only once within the <NULLS> element per table.
• The filtering feature for the null analysis is optional and can have multiple constraints.

The structure to perform this analysis is:

<ANALYSIS>
<TABLES>
<!-- analysis for null counts occurs here -->
</TABLES>
</ANALYSIS>

Within the <TABLE> element, the name attribute identifies the table to be analyzed. The targeted columns are identified within the <NULLS> element. The field attribute in the <NULL> element sets each column name. Apply filters to the analysis within the <CONSTRAINT> element. The following code illustrates the structure for the a null and padded space count analysis:

<TABLE name="table name">
<!-- May contain one or more columns -->
<NULLS><!-- With no constraints -->
<NULL field="column name"/><!-- With constraints -->
<NULL field="column name">
<!-- Constraint feature is optional.
May contain one or more constraints. -->
<CONSTRAINT field="column name" operator="operator"
value="filter value" />
        </NULL>
    </NULLS>
</TABLE>

The following XML code sample is an example of the correct structure:

<TABLE name="ACCT">
    <NULLS>
        <NULL field="ACCT_TYPE1_CD"/>
        <NULL field="RGSTN_TYPE_CD">
            <CONSTRAINT field="DATA_DUMP_DT" operator="EQ"
                value="15-NOV-2006" />
        </NULL>
    </NULLS>
<TABLE name="ACCT">

This code executes the following queries:

SELECT sum(case when ACCT_TYPE1_CD is null then 1 else 0 end)as NULL_CT0,
sum(case when ACCT_TYPE1_CD <> ltrim(rtrim(ACCT_TYPE1_CD))
then 1 else 0 end) as SPACE_CT0,
sum(case when RGSTN_TYPE_CD is null
and DATA_DUMP_DT='15-NOV-2006' then 1 else 0 end) as NULL_CT1,
sum(case when RGSTN_TYPE_CD <> ltrim(rtrim(RGSTN_TYPE_CD))
and DATA_DUMP_DT='15-NOV-2006' then 1 else 0 end) as SPACE_CT1
FROM ACCT a