DQL Statements

Whether you build your queries using Guided Query or the CREATE function, your query uses the same DQL statements. These statements instruct CA Dataquery 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 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.

The following chart summarizes the DQL statements. Statements appear in the order in which they should appear in a query.

Required YES/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 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 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.