SQL Statements for CA Dataquery

Whether you build your queries using Guided Query or the CREATE function, your query uses the same SQL clauses. These clauses instruct dqd to search a table, limit the search, join one or more tables to the first table, group data, sort the data for presentation, and perform mathematical functions.
datacom
Whether you build your queries using Guided Query or the CREATE function, your query uses the same SQL clauses. These clauses instruct
Dataquery
to search a table, limit the search, join one or more tables to the first table, group data, sort the data for presentation, and perform mathematical functions.
Dataquery
SQL queries are English-like clauses whose keywords are SELECT, FROM, WHERE, HAVING, GROUP BY, and ORDER BY. These clauses together form an SQL SELECT statement. The UNION keyword can be used to combine SELECT statements. Also, SQL clauses can use the same basic elements, like expressions and search conditions.
Only one SQL command is allowed per query.
Topics discussed on this page include:
SQL Mode Limitations
Review the following limitations when using
Dataquery
in SQL Mode.
  • The sum of the lengths of all the columns in an SQL query cannot exceed the DQESIZE parameter in the System Option Table.
  • An SQL query report cannot be formatted until the SQL query is validated.
  • SQL language verbs cannot be translated.
  • The CANCEL FIND function cannot be used to cancel an SQL query.
  • A joined view cannot be updated.
  • The number of tables/views in a single query cannot exceed 10.
  • Terms cannot be used.
  • The length of an authorization ID cannot exceed 18 characters.
  • An internal table may be exceeded if excessive amounts of the following (or combination of the following) have been specified in a query:
    • Number of columns selected in a query
    • Number of alternate headings in a report format
    • Number of control breaks in a report format
    • Number of column functions in a report format
    • Number of long literals specified for column values in an insert query
  • Only one SQL command is allowed per query.
SQL Keywords Summary
The following chart summarizes the SQL keywords used most often in queries. The keywords appear on the chart in the order in which they are used in an SQL query or dialog.
Keyword:
Operand:
Explanation:
SELECT
selection-list
Starts an SQL query or dialog. Specifies the data to be retrieved. The items in a selection list can be: column names, mathematical functions, arithmetic expressions, and literal constants.
FROM
table-names
Identifies the table or tables where data can be found.
WHERE
predicate
Tests each row in the named tables. If the result of the test is true, the row is selected. A predicate can be a comparison or a special search condition.
GROUP BY
column-names
Arranges data in groups. Usually used when needed for applying functions (like SUM) to groups of data.
HAVING
predicate
Used with GROUP BY to retrieve rows whose groups meet the search condition.
ORDER BY
select-list-items
Lists column names to specify the order in which data is to be presented. Columns listed must be in the SELECT clause. Ascending order is the default but DESC (descending) can be specified.
Other Acceptable SQL Keywords
Additional SQL keywords can also be submitted with
Dataquery
. You must have special authorization from your
Dataquery
Administrator to use these keywords in
Dataquery
.
Data definition authorization allows you to use:
  • ALTER
  • CREATE INDEX
  • CREATE SYNONYM
  • CREATE TABLE
  • CREATE VIEW
  • COMMENT ON
  • DROP
  • GRANT
  • REVOKE
Data maintenance authorization allows you to use:
  • DELETE
  • INSERT
  • UPDATE