Creating and Using Terms › Background for Examples

Background for Examples

Imagine you are a manager who frequently creates queries that select only data for the Southwest region, which is composed of all zip codes beginning with a 75. You print different kinds of reports on different kinds of data for the same region. You think you can simplify your daily work by creating terms to eliminate repetitive parts of your queries.

A typical query you can create looks like this:

      FIND ALL CAI-CUST-REC
          WITH ZIP EQUAL '75#'
      RELATED BY CUST-ORDID-KEY TO CAI-ORDERS-REC
          WITH DATE GTE 85
      SET DISCOUNT = UNIT-PRICE * DISC-PCT
      SORT BY (SLMN-ID)
      PRINT FROM CAI-CUST-REC
           SLMN-ID
           CUST-ID
           CITY
           STATE
           PHONE
         FROM CAI-ORDERS-REC
           ORD-ID
           TERMS
           SHIP-DT
           DISCOUNT
           (ORDER-TOTAL)
       WHEN SLMN-ID BREAKS
        DO 'AVERAGE ORDER AMOUNT' AVG ORDER-TOTAL

Explanation

The WITH clause in this query is an example of using literal masking to tell CA Dataquery to find specific values in specific positions of the column. The # character tells CA Dataquery that any other characters can occur in the remaining positions of the column. For complete instructions on using this kind of selection criteria, see the CA Dataquery Reference Guide.