In this step, you:
CA OLQ Release nn.n *** Column Select *** -> Page 1 of 2 124000 Select columns, specify selection criteria and press the ENTER key Columns currently selected: 0 Selection criteria _ EMPLOYEE x 02 EMP-ID-0415 * _ 02 EMP-NAME-0415 _ 03 EMP-FIRST-NAME-0415 * x 03 EMP-LAST-NAME-0415 * _ 02 EMP-ADDRESS-0415 _ 03 EMP-STREET-0415 x 03 EMP-CITY-0415 eq boston _ 03 EMP-STATE-0415 _ 03 EMP-ZIP-0415 _ 04 EMP-ZIP-FIRST-FIVE-0415 _ 04 EMP-ZIP-LAST-FOUR-0415 x 02 EMP-PHONE-0415 _ 02 STATUS-0415 Additional Selection Criteria: Proceed to Selection Criteria Screen? N Y/N 1=HELP 3=QUIT 4=MESSAGE 6=MENU 8=FWD PA2=REFRESH
More about selection criteria
Selection criteria are logical expressions you use to tell CA OLQ which rows of data you want to include in your report. You don't have to specify any selection criteria. If you don't, CA OLQ retrieves all rows from the record.
If you specify... Your report looks like... ┌────────────────────────────────────────┐ │ │ │ EMPLOYEE Report │ │ mm/dd/yy │ │ │ │ Emp ID Last Name City State │ │ │ EMP-ID-0415 EQ '0075' ────────────────-> 0075 Lanzarotta Lowell MA │ │ │ └────────────────────────────────────────┘ ┌────────────────────────────────────────┐ │ │ │ EMPLOYEE Report │ │ mm/dd/yy │ │ │ │ Emp ID Last Name City State │ │ │ EMP-CITY-0415 EQ 'SHELBURNE FALLS' ───-> 3302 Elopoulos Shelburne Falls MA │ │ 3871 Mahoney Shelburne Falls MA │ │ 4230 Ho Shelburne Falls MA │ │ │ └────────────────────────────────────────┘ ┌────────────────────────────────────────┐ │ │ │ EMPLOYEE Report │ │ mm/dd/yy │ │ │ │ Emp ID Last Name City State│ │ │ EMP-STATE-0415 NE 'MA' ───────────────-> 2789 Vangelis Buckatunna MS │ │ 5558 Runningbrook Casper WY │ │ │ └────────────────────────────────────────┘ ┌────────────────────────────────────────┐ │ │ │ EMPLOYEE Report │ │ mm/dd/yy │ │ │ │ Emp ID Last Name City State │ │ │ EMP-CITY-0415 MATCHES ───────────────-> 1164 Rokoski Waltham MA │ "WAL@@@@@" │ 2112 Walesa Walden MA │ │ 3881 Li Walpole MA │ │ 4003 Barrett Waltham MA │ │ │ └────────────────────────────────────────┘
How to specify selection criteria
Each column has its own Selection criteria field. If you want to retrieve rows based on the value in a certain column, fill in the Selection criteria field next to that column. For example:
Selection criteria
X 02 COLUMN1
_ 02 COLUMN2
X 02 COLUMN3 = 2000
X 02 COLUMN4 > 5 * COLUMN1
The following table gives examples of the types of operators you can include in your selection criteria.
|
Type of Operator |
Operator |
Description |
Example |
|---|---|---|---|
|
Logical comparison operators |
= |
Equal to |
= 100 = 100 to 500 (exclusive) = 100 thru 500 (inclusive) |
|
|
¬= |
Not equal to |
¬= COLUMN2 |
|
|
> |
Greater than |
> COLUMN1 * COLUMN2 |
|
|
>= |
Greater than or equal to |
>= COLUMN4 + 100 |
|
|
< |
Less than |
< COLUMN4 - COLUMN1 |
|
|
<= |
Less than or equal to |
<= 750 / COLUMN4 |
|
Arithmetic operators |
+ |
Addition |
COLUMN1 + COLUMN2 |
|
|
- |
Subtraction |
COLUMN1 - 5 |
|
|
* |
Multiplication |
COLUMN1 * 1/2 |
|
|
/ |
Division |
COLUMN1/COLUMN2 |
|
MATCHES and CONTAINS characters |
* |
Stands for any character |
contains "Z**05" |
|
|
@ |
Stands for any alphabetic character |
matches "NEW@@@@@@@@@@" |
|
|
# |
Stands for any numeric character |
matches "669####" |
The IN clause
You can include the IN clause in your selection criteria. The IN clause is a subset of the SQL SELECT statement that compares your column value to a list of data values.
For complete syntax and syntax rules, refer to the CA OLQ Reference Guide.
IN IN yields a true comparison if the column value matches one or more of the data values. For example, you can compare the value of the EMP-LAST-NAME field to the following IN clause:
IN ('JONES', 'TANAKA', 'ANDERSON')
NOT IN NOT IN yields a true comparison if the column value does not match or more of the data values. For example, you can compare the value of the EMP-LAST-NAME field to the following IN clause:
NOT IN ('JONES', 'TANAKA', 'ANDERSON')
The LIKE clause
You can also include the LIKE clause in your selection criteria. The LIKE clause is a subset of the SQL SELECT statement that searches for a pattern string.
For complete syntax and syntax rules, refer to the CA OLQ Reference Guide.
How to use the LIKE clause
Your LIKE clause contains two parts:
LIKE
LIKE determines whether a column expression contains a pattern string. LIKE yields a true comparison if the column contains the pattern. For example, you can compare the EMP-LAST-NAME field to the following LIKE clause:
LIKE 'MAC%'
This comparison is true if the last name is any number of characters long and begins with the string MAC.
NOT LIKE
NOT LIKE yields a true comparison if the column does not contain the pattern. For example, you can compare the OFFICE-CODE field to the following NOT LIKE clause:
NOT LIKE '002'
This comparison is true if the office code is anything except for 002.
Pattern string
The pattern string is what is compared to the column value. The pattern can contain:
The LIKE clause is summarized in the table below.
|
Object String |
Pattern String |
Example of Syntax |
Example of True comparison |
|---|---|---|---|
|
Underscore (_) |
Any single character |
NAME LIKE 'S__' |
True if NAME is exactly 3 characters long and the first character is S |
|
Percent sign (%) |
Any sequence of zero or more characters |
NAME LIKE '%C__' |
True if NAME is 3 or more characters long and the third from last character is C |
|
Single alphanumeric character |
Exact match to that alphanumeric character |
NAME LIKE 'MAC' |
True if NAME is MAC |
|
Escape character + underscore (_) |
Exact match to the underscore (_) |
PARTNUM LIKE '*_115' ESCAPE '*'1 |
True if PARTNUM is __115 |
|
Escape character + percent sign (%) |
Exact match to the percent sign (%) |
PARTNUM LIKE '*%15' ESCAPE '*'1 |
True if PARTNUM is *%15 |
|
Escape character alone |
Exact match to the escape character |
PARTNUM LIKE '***' |
True if PARTNUM is *** |
1The escape character can be any single alphanumeric character and is set by specifying ESCAPE 'escape-character' in your SELECT statement.
The AND operator
If you enter selection criteria for more than one column, CA OLQ combines the expressions by placing the AND operator between them. AND means that a row must meet both conditions in order to be retrieved.
For example, when you specify:
Selection criteria
X 02 COLUMN1
_ 02 COLUMN2
X 02 COLUMN3 eq 2000
X 02 COLUMN4 gt 5 * COLUMN1
CA OLQ produces:
COLUMN3 eq 2000 and COLUMN4 gt 5 * COLUMN1
The OR operator
You may not want to combine your expressions with AND operators. As an alternative, you can type the OR operator at the beginning of any expression. OR means that a row need only meet one of the conditions in order to be retrieved.
For example, when you specify:
Selection criteria
X 02 COLUMN1
_ 02 COLUMN2
X 02 COLUMN3 eq 2000
X 02 COLUMN4 or gt 5 * COLUMN1
CA OLQ produces:
COLUMN3 eq 2000 or COLUMN4 gt 5 * COLUMN1
Selection criteria for unchosen columns
You can specify criteria for columns that do not appear on your report. CA OLQ tests the values in such columns when it chooses which rows to retrieve, but does not display those values on the report.
Built-in functions
CA OLQ provides built-in functions that you can use in your selection criteria. Built-in functions are a shorthand way of performing common calculations (such as square root) and string manipulations (such as concatenation).
For more information on CA OLQ built-in functions, refer to the CA OLQ Reference Guide.
|
Copyright © 2013 CA.
All rights reserved.
|
|