SQL Query

Introducing CA Dataquery Query Creation › Query Creation Summaries › SQL Query

SQL Query

SQL queries are made up of clauses which can be as simple or as complex as you want them to be. You can write these clauses yourself using the CREATE function or you can enter specifications on the GUIDE panels and have CA Dataquery construct the clauses (and the query) for you.

The following chart summarizes the SQL clauses 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.

Additional SQL keywords can be used in CA Dataquery, but have very specific uses not normally needed for a query. For a list of those words and information about their use, see the CA Datacom/DB SQL User Guide.

Example

If you are working in SQL Mode and you want to create a list of your Texas customers with a 2008 through 2010 shipped order quantity equal to or greater than 10, your query might look like this.

Sample Query

  SELECT REP-ID, CUST-ID, TERMS, ORDER-TOTAL
  FROM CA-SLSHST-TBL, CA-ORDERS-TBL
  WHERE CUST-ID EQ CUST-NO
    AND SHIP-QTY GTE 10
    AND ORD-YR GTE 08
  ORDER BY REP-ID

The syntax is written to sort the results by sales representative, and include customer IDs, terms, and order amounts. The query joins two tables and orders and reports data in columns from each table. It also specifies selection criteria for the rows to be read during its search for data.

Results - Sample Report from Two Tables


05/08/11                                                         PAGE  1
16:39:07                                                          DETAIL

  REP-ID  CUST-ID     TERMS            ORDER-TOTAL
  -------  -------     ---------------  -----------
  07585    I4790       NET30            0000931.72
  07585    I4790       NET30            0000353.50
  07585    I4790       NET30            0002710.61
  07585    I4790       NET30            0000076.00
  14830    H4130       NET30            0000181.78
  14830    H4130       NET30            0003595.75
  14830    H4130       NET30            0002258.10
  14830    H4130       NET30            0000190.65
  18365    M5750       NET30            0002032.10

There is much more you can do with a query, like setting up dialogs for other users.