Introducing CA Dataquery Query Creation › Query Creation Summaries › DQL Query

DQL Query

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

The following shows the basic tasks a DQL query can perform.

FIND

Starts a query that produces a report, specifying tables to search. Can specify number of rows to find. Can contain clauses that narrow the search and join more than one table.

COUNT

Starts a query that produces a count of rows in a table. Can specify types of rows and join more than one table if WITH and relationship clauses are added.

SET

Creates a report result for each detail row by combining existing columns mathematically, for example,
SET GROSS = SALES + COMMISSION.

SORT BY

Sorts rows found. Can specify grouping of rows found (control breaks).

PRINT

Requests columnar report format.

DISPLAY

Requests report format that displays one row per panel (or page). Can specify totaling and other numeric functions.

Example

  FIND ALL CAI-SLSHST-TBL
     WITH SHIP-QTY GTE 10
    RELATED BY CUST-ID VIA CUST-ID TO CAI-ORDERS-TBL
     WITH ORD-YR GTE 85
  SORT BY CAI-SLHST-TBL REP-ID
  PRINT FROM CAI-SLSHST-TBL
             REP-ID
             CUST-ID
        FROM CAI-ORDERS-TBL
             TERMS
             ORDER-TOTAL

The sample query joins two related tables by naming the keys they have in common and tells CA Dataquery to print columns from each table. It also specifies selection criteria for the rows it wants CA Dataquery to read during its search for data. Later chapters take you through building basic queries. The CA Dataquery Reference Guide provides all the information you need about building queries that join tables.

Results

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