DQL Statements

Whether you build your queries using Guided Query or the CREATE function, your query uses the same DQL statements. These statements instruct dqd 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.
datacom150
Whether you build your queries using Guided Query or the CREATE function, your query uses the same DQL statements. These statements instruct 
CA Dataquery™ for CA Datacom®
 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 chapter provides detailed information about DQL syntax. The 
CA Dataquery™ for CA Datacom®
 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, turn to Reading Syntax Diagrams.
 
Contents
 
 
 
DQL Statements Summary
The following chart summarizes the DQL statements. Statements appear in the order in which they should appear in a query.
 
RequiredYES/NO
 
 
DQL Keyword
 
 
Description
 
YES
FIND
or
COUNT
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 
WITH
 to limit the rows counted and a join keyword to retrieve rows from more than one table.
NO
WITH
Limits the search to rows which match specified selection criteria. Can use with 
FIND
 and relationship keywords to limit the rows which are retrieved. Uses comparison and logical operators to build logical expressions for the selection criteria.
NO
RELATED
OUTER-JOINED
LEFT-JOINED
RIGHT-JOINED
OUTER-DISJOINED
LEFT-DISJOINED
RIGHT-DISJOINED
Relationship keywords that tell 
CA Dataquery™ for CA Datacom®
 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.
NO
SET
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.
NO
SORT
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.
YES
(If output is wanted.)
PRINT
or
DISPLAY
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.
NO
WHEN/DO
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 
CA Dataquery™ for CA Datacom®
 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 
CA Dataquery™ for CA Datacom®
 in DQL Mode. This section describes the items that are internally monitored by 
CA Dataquery™ for CA Datacom®
 in DQL Mode. The following subsections are organized by DQL keywords, such as syntax, DRAW command, Guided Query, and dialogs.
Internal 
CA Dataquery™ for CA Datacom®
 Table
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 
    CA Dataquery™ for CA Datacom®
    .
  • 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.