DQL Statements › COUNT Statement

COUNT Statement

Purpose

The COUNT statement specifies the information to be located in the database. The COUNT statement contains three major parts: the COUNT, the selection criteria and the relationship criteria. Criteria can be omitted.

Operation

The COUNT statement must be the first entry of a COUNT query. When you execute a COUNT statement, CA Dataquery locates the specified data and reports on the number of rows found by displaying a message on the screen.

Syntax Diagram

Following is the syntax for the COUNT statement.

►►─ COUNTtable-name ─┬────────────┬─┬────────┬─┬───────────────┬──────────────►
                     └─ (status) ─┘ └─ ROWS ─┘ └─ with-clause ─┘

 ►─┬───────────────────────┬──────────────────────────────────────────────────►◄
   └─ relationship-clause ─┘

Explanation

The following explains each keyword in a COUNT statement.

table-name

Represents the name of the primary table containing the rows CA Dataquery is to count. Only those tables shown in your Directory of Tables are available for query access.

(status)

(Optional) If authorized, use to specify a Datadictionary definition of a table that is being tested by programmers responsible for its maintenance. If the FIND or COUNT statement specifies a TEST status for a table, all subsequent statements in the same query reference the same status and version of the table, unless a different status is specified. The following chart describes each status you can specify.

Note: The status must be enclosed in parentheses, and there must be no spaces between the table name and the left parenthesis.

TEST

Finds the named table in the first test version that meets the specifications.

Tnnn

(Where nnn refers to the version number of the test status you want.) Finds the named table in the specified test version.

PROD

PRODuction is the default status if not specified.

ROWS

Used for readability only.

with-clause

Represents one or more logical expressions which specify the criteria CA Dataquery is to use to qualify rows to be counted. A WITH clause contains the criteria which qualify the rows. Only those rows which meet the specified criteria in the WITH clause are counted, as in the following example:

COUNT CAI-SLSHST-REC ROWS
          WITH SHIP-QTY GT 0

The selection criteria can consist of multiple conditions which serve to limit the number of rows to those which meet all conditions. See WITH Clauses for more information.

You can decide not to include any selection criteria. This causes CA Dataquery to count all the rows in the specified table.

relationship-clause

Represents a relationship expression which relates the data in two or more tables. A relationship clause establishes the relationship between tables. When you use RELATED BY to join tables, you can access all the data contained in each row of the relationship, as in the following example:

     COUNT CAI-SLSHST-REC ROWS
          WITH SHIP-QTY GT 0
     RELATED BY SLMN-ID TO CAI-ORDER-REC ROWS
          WITH ORD-YR LT '87'

When you use an outer join keyword, you can retrieve unrelated rows, when needed. See Outer Joins for details. Multiple relationship expressions can exist in a COUNT statement. In addition, you can specify selection criteria for each relationship clause. See Relationship Clauses for more information.