WHERE (qualify record selection)
WHERE qualifies record selections.
If the condition is matched, control flows to the next statement.
Syntax
where
- Vn is the field name of a CMRSTATS, CMRDETL, or CTGDETL record. For more information, see Reference-for-PERFORMANCE-REPORTER
- value can be a literal character string or a defined variable.
WHERE is written after the SELECT statement and is used with any of the following operators to qualify a selected record:
Operator | Description |
|---|---|
= | equal to |
> | greater than |
< | less than |
¬ = | not equal to |
¬ < | greater than or equal to |
¬ > | less than or equal to |
AND | NA |
OR | NA |
Usage
The argument in the WHERE clause can be repeated with other arguments, appropriately using the operators AND and OR. Only one variable can be qualified per line. The following example restricts the selection to activity by user DJD, at terminal SC32, with a response time that is greater than two seconds:
WHERE T6ETMID = 'SC32'
AND T6EOPID = 'DJD'
AND T6ERESP > 2
Alternative data can be selected for the same variable by adding an OR operator to the right of the WHERE expression. An example follows:
WHERE T6ETRID = 'CSSN' OR 'CSSF' OR 'CSTT'
This example selects 6E records created by transactions CSSN, CSSF, or CSTT.
This same WHERE condition could also be repeated with OR as follows:
OR T6ETRID = 'CSSF'
OR T6ETRID = 'CSTT'
An AND operator with WHERE combines specific data from selected records. The following example selects 6E records that had activity on 09/27/1998 between 10:00 A.M. and 11:00 A.M.:
SET FTIME = '09:59:59'
SET LTIME = '11:00:01'
SELECT TYPE 6E RECORDS FROM CMRDETL
WHERE CMRDATE = DATE
AND CMRTIME > FTIME
AND CMRTIME < LTIME
These PRL statements select data from 6E records based upon the date and time specified in the CMRDATE and CMRTIME fields that
- match the 09/27/1998 date assigned to the DATE variable
- have a time stamp (recorded in the CMRTIME common header field) greater than the time assigned to the FTIME variable (start at 10:00)
- have a time stamp less than the time assigned to the LTIME variable (end at 11:00)
Any number of AND or OR conditions can be used with a WHERE statement. However, a condition must be defined with either all ANDs or all ORs because combining them may create a logically ambiguous condition.
If ¬= is used with more than one AND or OR clause, the not condition inverts the logic and should be avoided as follows:
- Use an all positive condition.
- Relegate a single ¬= to the last clause.
The following example shows the use of OR statements to select alternative data from the same field (T6ETRID - transaction identifier). An AND statement adds another required condition to the WHERE condition that further restricts data collected from the field. The expression ¬= is used last and qualifies the record selection to identified terminals.
WHERE T6ETRID = 'CSSN' OR 'CSSF' OR 'CSTT'
AND T6ETMID ¬= ' '
Example
SET TITLE = 'T E R M I N A L E R R O R S'
SELECT TYPE 6E RECORDS FROM CMRDETL
WHERE T6EPGNM = 'DFHACP '
USING CMRDATE CMRTIME T6ETRID T6ETMID T6EOPID T6ECPUR T6ERESP REPORT
END
Related topic