This step tells CA Dataquery how to select only those rows wanted in the report. CA Dataquery includes data in the report only if it matches specified selection criteria.
Concepts
These are the major concepts related to this step:
WITH
Tells CA Dataquery that the data in the rows must match the specifications described in the selection criteria.
Selection-criteria
Describes the data you want to use in qualifying a row for inclusion in your report. You can use column names, comparison operators, literal values (character strings or numeric values) and masking to build the logical expression containing your selection criteria.
Column name
Tells CA Dataquery which column in the table to search for the qualifying data. You can specify selection criteria for up to 15 columns per table to further limit the data for your report. You use the logical connectors, AND and OR to construct the multiple conditions. Details on constructing WITH statements with multiple conditions are contained in the CA Dataquery Reference Guide. If you specify the name of a column which is a repeating field, you must use subscripts to identify the occurrence. See Repeating Fields for more information.
Comparison operator
Tells CA Dataquery how to compare the value to the data. For example, if a zip code value is 75081, and you want only rows with that zip code, you will select EQUAL as the comparison operator and complete the expression so that CA Dataquery searches the ZIP column for data EQUAL to 75081.
You can select from the operators described in the following chart. For detailed information about these operators, see the CA Dataquery Reference Guide.
|
Operator |
Alternate Entry |
Explanation |
|---|---|---|
|
EQUAL |
EQ, = |
Select exact value. |
|
NOT EQUAL |
NE, ¬= |
Select all data that is not the exact value. |
|
GREATER THAN |
GT, > |
Select data with higher value. |
|
GREATER THAN OR EQUAL TO |
GTE |
Select data of equal or higher value. |
|
LESS THAN |
LT, < |
Select data lower than value. |
|
LESS THAN OR EQUAL TO |
LTE |
Select data of equal or lower value. |
|
CONTAINING |
none |
Select data that contains value (you do not need to know position of value within column data). |
|
NOT |
¬ |
Select all data that is not related as specified (can use before any of the above operators except NOT EQUAL). |
Literal value
Tells CA Dataquery which value to search for. This value can be expressed as a character string, a numeric value, a column name, or an arithmetic expression.
Character string
A character string must be enclosed in apostrophes. For example, you can specify a search for rows where the CITY column contains the value 'DALLAS.'
Numeric value
A numeric value does not need to be enclosed in apostrophes. You can include decimal positions and a leading plus (+) or minus (-) sign if the column is defined as signed. Following is a sample of how the panel looks now.
=> --------------------------------------------------------------------------DQD10 DATAQUERY: EDITOR CURRENT TABLE: CAI-DETAIL-TBL ------------------------------------------------------------------------------- NAME: USER-SAMPLE TYPE: QUERY STATUS: PRIVATE DESCRIPTION: TOTAL ORDERS PER CUSTOMER -- AVERAGE PRICE ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+. .. ================================ T O P ===================================== 01 FIND ALL CAI-DETAIL-TBL ROWS .. =========================== B O T T O M ================================== ------------------------------------------------------------------------------- <PF1> HELP <PF2> RETURN <PF3> DISPLAY COLUMNS <PF4> DISPLAY KEYS <PF5> DISPLAY ALL <PF6> LIST TABLES <PF7> BACKWARD <PF8> FORWARD <PF9> TEMPLATE <PF10> VALIDATE <PF11> RIGHT/LEFT <PF12> PROCESS MODE
Action
On the panel, insert the following two lines after line 01:
You can insert one line by typing I without the number, or you can insert five lines by typing I5. Remember, the blank lines are inserted after the line where you use the command.
=> --------------------------------------------------------------------------DQD10 DATAQUERY: EDITOR CURRENT TABLE: CAI-DETAIL-TBL ------------------------------------------------------------------------------- NAME: USER-SAMPLE TYPE: QUERY STATUS: PRIVATE DESCRIPTION: TOTAL ORDERS PER CUSTOMER -- AVERAGE PRICE ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+. .. ================================ T O P ===================================== 01 FIND ALL CAI-DETAIL-TBL ROWS 02 03 .. =========================== B O T T O M ================================== ------------------------------------------------------------------------------- <PF1> HELP <PF2> RETURN <PF3> DISPLAY COLUMNS <PF4> DISPLAY KEYS <PF5> DISPLAY ALL <PF6> LIST TABLES <PF7> BACKWARD <PF8> FORWARD <PF9> TEMPLATE <PF10> VALIDATE <PF11> RIGHT/LEFT <PF12> PROCESS MODE
Action
On the panel, type WITH SHIP-QTY GT 0 in the first blank line (02).
You can type the WITH statement and selection criteria without using the online aids, such as displaying the query template or a list of columns in the table. If you know the format for a statement and the information to be used in the statement, you can simply type in the statement. In the above statement, SHIP-QTY is the name of the column that CA Dataquery is to search for a specific value, which is 0. Choosing the comparison operator GT makes sense because a value equal to 0 means that no item has been shipped. By specifying GT as the operator, the query will retrieve only those rows where an item has been shipped, that is, the value will be 1 or greater.
Completed Example
=> --------------------------------------------------------------------------DQD10 DATAQUERY: EDITOR CURRENT TABLE: CAI-DETAIL-TBL ------------------------------------------------------------------------------- NAME: USER-SAMPLE TYPE: QUERY STATUS: PRIVATE DESCRIPTION: TOTAL ORDERS PER CUSTOMER -- AVERAGE PRICE ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+. .. ================================ T O P ===================================== 01 FIND ALL CAI-DETAIL-TBL ROWS 02 WITH SHIP-QTY GT 0 03 .. =========================== B O T T O M ================================== ------------------------------------------------------------------------------- <PF1> HELP <PF2> RETURN <PF3> DISPLAY COLUMNS <PF4>DISPLAY KEYS <PF5> DISPLAY ALL <PF6> LIST TABLES <PF7> BACKWARD <PF8>FORWARD <PF9> TEMPLATE <PF10> VALIDATE <PF11> RIGHT/LEFT <PF12>PROCESS MODE
Options
Join Tables
You can use the query template to select the WITH format and use <PF3> DISPLAY COLUMNS to display and select the columns in the table the query will search. If you want the search criteria to further limit the data that is returned, you can build a logical expression containing multiple conditions that the data must match. Your WITH clause could also contain arithmetic expressions.
This query searches only one table, but if you need to search more than one table, you can join the first table with a second using the RELATE statement. You could also specify additional selection criteria for data in the second table. Information on how to use the RELATE statement to join two or more tables is contained in the CA Dataquery Reference Guide. You can also use an outer join keyword to specify selection of rows left out of an equijoin. See the CA Dataquery Reference Guide for details.
Literal Masking
You can use masking to locate data in particular positions within your data. When you use masking, you must know the format of the data and the position of the value within that data. The positions that you do not want to be compared need to be masked with pound signs (#). For example, you want to locate all items in your inventory which have an ID number ending in XYZ. You know the ID for each item is six characters long and that you want to mask the first three digits. Your selection criteria for the search would be:
WITH ITM-ID = '###XYZ'.
Your site may have chosen a different character for this purpose. To verify your site's mask character, view the SYSTEM PROFILE help topic or contact your CA Dataquery Administrator.
Containing
CONTAINING is similar to masking, except that you do not want to compare the values by position. Instead, you specify that the column must contain the value, no matter if the value appears at the beginning, middle or end of the column's value. For example, if you want to locate all items in your inventory which have XYZ in the item ID, your selection criteria for the search would be:
WITH ITM-ID CONTAINING 'XYZ'.
You cannot use CONTAINING with numeric type columns.
|
Copyright © 2014 CA.
All rights reserved.
|
|