DQL Statements

Whether you build your queries using Guided Query or the CREATE function, your query uses the same DQL statements.
Whether you build your queries using Guided Query or the CREATE function, your query uses the same DQL statements. These statements instruct
to search a table, limit the search, relate two or more tables, create temporary results, sort the data, determine the format of the report and perform mathematical functions. DQL statements are English-like sentences and clauses whose keywords are verbs like FIND, SORT, or PRINT.
This section provides detailed information about DQL syntax. The
verbs appear in alphabetic order with the verb name listed on the top outside corner of the page. For help in understanding the syntax diagrams, see Reading Syntax Diagrams.
The following topics are discussed on this page:
DQL Statements Summary
The following chart summarizes the DQL statements. Statements appear in the order in which they should appear in a query.
DQL Keyword
FIND starts a query and specifies how many rows to retrieve in the table being searched.
COUNT begins a query that counts the number of rows in a table and does not produce a report.
Can use either with
to limit the rows counted and a join keyword to retrieve rows from more than one table.
Limits the search to rows which match specified selection criteria. Can use with
and relationship keywords to limit the rows which are retrieved. Uses comparison and logical operators to build logical expressions for the selection criteria.
Relationship keywords that tell
to search more than one table to retrieve specific rows. Retrieves those that are related by a key or column and those that are not, depending on the keyword used. See Relationship Clauses (DQL) and Outer Joins.
Creates a temporary result to hold results of calculations involving column data from the database or literal values. Can perform mathematical functions, such as finding the average or maximum of a list of specific values, within the arithmetic expression.
Specifies key or columns with which to sort the data in a specific order (UP or DOWN) for report output. Can also specify control break columns.
(If output is wanted.)
PRINT produces a report in columnar format for specified database columns or temporary results. Can also specify report title, alternate headings for columns, the accumulation of totals for numeric columns at a control break, and alternate edit patterns for numeric data in the report output.
DISPLAY produces a report in one row per page format listing specified database columns or temporary results. Can also specify alternate headings for columns and alternate edit patterns for numeric data in the report output.
Specifies when a mathematical function or page break is to be performed and what column is the object of the mathematical function. Can specify mathematical function or page break based on a control break, or at the end of the report.
Performance Considerations
Using a COUNT statement rather than a FIND statement can significantly improve the performance of your query in its search for the specified rows. Because no data is retrieved, the search requires fewer resources. In some cases, the difference in performance will be small.
Using keys in the selection criteria for your WITH clause can significantly improve the performance of your FIND or COUNT statement in its search for the specified rows. The purpose of keys is to expedite the search for data. In some cases, specifying a column in your selection criteria can be almost as efficient as using a key. Using an arithmetic expression in your selection criteria will nearly always require more overhead than specifying a key or column in the WITH clause.
When you specify relational criteria in a relationship clause, again the use of keys can be more efficient than specifying a column or a literal value to join the tables your query searches.
Your choice of operators in the WITH clause can also affect the efficiency of the FIND. For example, using a key and EQUAL as the operator for your selection criteria can be more efficient than choosing the combination of masking and another operator, such as GREATER THAN. See WITH Clauses (DQL) for details about operators.
When using a left join or left disjoin, as discussed on Outer Joins, performance should not be negatively impacted. Performing a right join or right disjoin could be costly for large tables. These joins are not reversible.
In the case of the join of three or more tables, the result of the processing would be as if the joins were done two at a time in the order written, with the result of the join of the first pair of tables being joined to the third table, and so on.
Getting Assistance
If you have concerns about the performance of your selection criteria and relationship criteria in a query, your Database Administrator or
Administrator can help you determine which combination of keys or columns and operators will make your query most efficient.
DQL Mode Limitations
Review the limitations in this section when using
in DQL Mode. This section describes the items that are internally monitored by
in DQL Mode. The following subsections are organized by DQL keywords, such as syntax, DRAW command, Guided Query, and dialogs.
An internal table is used to keep track of the following:
  • Columns and SET statements in an INSERT query.
  • Columns and keys referenced in a query.
  • Titles.
  • Alternate headings (whether from DD or specified in the query).
  • All the simple columns that make up a key.
  • All column entries. Only columns with class S (simple), C (compound), or F (filler) can be accessed in DQL Mode.
  • Repeating field subscripts. (A maximum of two levels is allowed.)
  • Totals and column functions.
  • Restricted conditions.
  • Edit patterns (whether from DD or specified in the query).
An excess of one or more of these items can make this internal table overflow.
Exported, Saved, Kept, and Extracted Sets
Following are important limitations to observe when using sets in DQL Mode.
  • The total length of all columns used in the PRINT statement for an Export cannot exceed 4088 characters.
  • The maximum number of Saved Sets for a single user is determined by the MXSETS parameter in the System Option Table.
  • An Extracted Saved Set cannot be used by
  • The maximum size of an extracted file cannot exceed the XTRBLKS parameter in the System Option Table.
DQL Mode Nesting Limitations
Terms cannot be nested more than four levels deep.
An arithmetic expression cannot have more than five levels of nested parentheses.
A SET statement cannot have more than ten levels of nested parentheses.