Specifying Result Tables › Counting

Counting

The COUNT(*) function lets you tally the number of rows in the result table. The COUNT(DISTINCT column-name) form of this function returns the number of distinct values in the specified column.

To improve performance when only the count of rows in a table are desired, the value returned for COUNT(*) is pulled from the CXX information when:

• COUNT(*) is the only item in the SELECT list
• there is no WHERE clause
• there is no JOIN

Note: This CXX information you obtain could at times be nominally out of date. If the most accurate information possible is required, add one of the restrictions mentioned above to the SELECT to force a count of the rows in the table. However, any changes to the table after the result table is generated are not going to be reflected in the COUNT that is obtained, either.

The following example uses the COUNT(DISTINCT column-name) function in a SELECT INTO statement.

 Problem
Find the number of unique customer states contained in CUSTOMERS.
 Solution

               .
               .
       (COBOL statements)
               .
               .
  1   EXEC SQL
  2       SELECT COUNT(DISTINCT STATE)
  3       INTO :WC-COUNT
  4       FROM CUSTOMERS
  5   END-EXEC.
               .
               .
       (COBOL statements)
               .
               .

Line 2

The COUNT(DISTINCT STATE) function tallies the number of distinct values in the STATE column of CUSTOMERS. The result of the SELECT statement is the result of the COUNT function, which is a number, not a result table.

Line 3

The INTO clause specifies that the value returned by the COUNT function is placed in the host variable :WC-COUNT. Hyphens can be used in a COBOL item which is referenced in an SQL statement.