HP 3000 Manuals HP 3000 Manuals
Chapter 6 SELECTION CRITERIA
INFORM/V allows you to produce a report that includes only a particular
part of the available data by specifying selection criteria--values that
the data must match in order to be included in the report. For example,
you can specify that the report contain information only for the first
six months of the year, or you can request a report for only certain
values of NAME Any data name shown on the Data Names Menu can be selected
for use in selection criteria.
Specifying selection criteria is generally a two-step process (the
exception is data-name to data-name comparison, which is discussed at the
end of this section). For most selection criteria, the first step occurs
on the Data Names Menu, where you specify the data names that will be
used in specifying criteria. The second step occurs on the Report
Process Menu, when you are producing the report. INFORM/V will ask you
to give the selection criteria for each data name that you specified in
the first step. The process is shown in Figure 6-1 and Figure 6-2.
The First Step
On the Data Names Menu, the user responds to the prompt
FOR SELECTION CRITERIA>
by entering the number of the data name that will later be used to
specify selection criteria.
An example of the interaction is shown in Figure 6-1. Here the user
enters 1 to tell INFORM/V that later selection criteria will apply to the
data name NAME.
_______________________________________________________________________________
| |
| INFORM/V HP32246v.uu.ff - (C) Hewlett-Packard Co. 1986 |
| |
| DATA NAMES IN ACCOUNTING INFORMATION |
| |
| 1: NAME 7: DESCRIPTION 13: PRICE |
| 2: ADDRESS 8: PURCHASE NUMBER 14: TOTAL |
| 3: ACCOUNT 9: PURCHASE DATE 15: SHIPDATE|
| 4: DATE 10: BACKORDERFLG 16: DELVDATE|
| 5: CREDIT 11: QUANTITY |
| 6: PRODUCT NUMBER 12: UNIT COST |
| |
| TYPE NUMBER(S) FOR DATA NAME(S): |
| |
| TO INCLUDE IN REPORT> 1,6,11 |
| TO SORT BY> 1 |
| TO SUBDIVIDE BY> 1 |
| TO SUMMARIZE FOR: NAME> 11 |
| FOR REPORT SUMMARY> 1COUNT,11AVERAGE |
| FOR SELECTION CRITERIA> 1 |
| |
| |
| |
_______________________________________________________________________________
Figure 6-1. Specifying Data Names for Selection Criteria
The Second Step
After the user tells INFORM/V to produce the report, the Report Process
Menu (Figure 6-2) appears, and the user is prompted for passwords.
INFORM/V then displays the prompt for selection criteria:
SELECTION CRITERIA FOR
data name>
_______________________________________________________________
| |
| INFORM/V HP32246v.uu.ff - (C) Hewlett-Packard Co. 1986|
| |
| REPORT PROCESS STARTED |
| |
| PASSWORD FOR ACTG> |
| |
| SELECTION CRITERIA FOR |
| |
| NAME> ALBERTSON JOHN B |
_______________________________________________________________
Figure 6-2. Specifying Selection Criteria
The user enters ALBERTSON JOHN B in response to this prompt, thereby
requesting information for only one value of NAME. The report produced in
this case is shown in Figure 6-3.
______________________________________________________________________
| |
| PROFIT REPORT |
| |
| NAME PRODUCT NUMBER QUANTITY PROFIT MARGIN |
| ---------------------------------------------------------------|
| |
| Albertson John B 90C 100 $200.00 |
| 60D 130 $19.50 |
| 50B 30 $37.50 |
| 50B 5 $6.25 |
| => 265* $263.25*|
| |
| *****END OF REPORT***** |
| |
| |
| |
| |
______________________________________________________________________
Figure 6-3. Report for a Specified Name
The data names that are specified for use in selection criteria are saved
as part of the report, but the values supplied on the Report Process Menu
are not. It is therefore easy to get the same kind of report for many
different subsets of data. For example, if the user saves the report
definition shown in Figure 6-1, INFORM/V will prompt for selection
criteria for NAME every time the report is produced in the future. The
user could therefore get the same kind of report for different
individuals, or for different groups of individuals.
General Forms for Selection Criteria
The example shown above uses a very simple reponse to the prompt for
selection values. More complex selection criteria are possible. There
are two different general forms that can be used in specifying selection
criteria, one of which it to use a wild card and the other of which uses
an operator. You can also connect different criteria for even more
restrictive or complex specifications.
Selection Using a Wild Card. The first general form for selecting values
is generally used with character data, but can also be used with numeric
data.
This form uses the wild-card character ^ within a string of literal
characters. When INFORM/V searches the data, it selects values in which
the literal characters match, and the wild cards can be anything. The
wild card character can be used before, after, or within the literal
string, and is interpreted as follows:
xyz^ A single wild-card character after a string of literal
characters tells INFORM/V to report data for all values
that begin with the characters specified. For example,
the selection criterion CHAMB^ would select CHAMBERLAIN,
CHAMBERLIN, CHAMBERS, and any other name that begins
with CHAMB.
xyz^^ Two wild-card characters appearing after a string of
literal characters tells INFORM/V to report data for all
values that have the specified characters in any
position. For example, the selection criterion BERT^^
would select BERTHOLD, ALBERTSON, HERBERT, and any other
name that contains the characters BERT.
^xyz A single wild-card character preceding a string of
literal characters tells INFORM/V to report data for all
values that end with the specified characters. For
example, the selection criterion ^SON would select
JOHNSON, ALBERTSON, HANSON, and any other name, of any
length, that ends in SON.
w^x^yz When wild-card characters are within the string of
literal characters, the exact position of each character
becomes important. The values that are found must be
the same length as the value given, and the characters
must match on a one-for-one basis. This form tells
INFORM/V to find all values in which the literal
characters appear exactly as specified, in the specified
positions, and the characters in the positions of the
wild-card characters can be anything. For example, the
selection criterion HANS^N would find both HANSEN and
HANSON, but would not find HANSAAN.
Selection Using a Comparison Operator. The second general form is
generally most appropriate to numeric data, but can be used with data of
any type.
It can be represented simply as follows:
[operator] value
where operator may be any of the following:
< or LT less than
<= or LE less than or equal to
> or GT greater than
>= or GE greater than or equal to
<> or NE not equal to
= or EQ equal to (this is the default when no operator is given)
and value can be any string or number (so long as it is appropriate to
the data name).
For example,
> 1 is equal to GT 1
and both selection criteria would select all records for which the data
name value is greater than one.
Connectors in Selection Criteria. It is possible to use two or more
general forms together in one selection criterion. Connectors that can
be used within or between general forms to specify more that one search
value are:
TO Specifies that the record reported must contain one of a
range of values bounded by the value before and the
value after this connector. For example,
7000 TO 10000
finds all values of the data name that are between 7000
and 10000 inclusive.
AND Specifies that the record must satisfy the expression
before and after this operator. For example,
>7000 AND <10000
specifies that the value must be greater than 7000 and
less than 10000.
OR Specifies that the record reported must satisfy the
expression either before this operator or after it. For
example,
94306 OR 94304
would retrieve only those records for which the data
value is 94306 or 94304.
If both AND and OR are included in the same specification, the AND takes
precedence over the OR. For example, in the following expression:
>1 AND <3 OR GT 10
the value must be either between 1 and 3 OR greater than 10.
need 4 If you enter one of the connectors as the last item before RETURN
on a line, INFORM/V responds with the following prompt:
(CONTINUATION LINE)>
This prompt gives you the opportunity to add to your selection criteria.
Examples of both general forms and connecters are shown in Table 6-1.
Table 6-1. Examples of Selection Criteria
---------------------------------------------------------------------------------------------
| | |
| Selection Criteria | Interpretation |
| | |
---------------------------------------------------------------------------------------------
| | |
| 20 TO 30 | From all the values of this data name |
| | select those between and including 20 and |
| | 30. |
| | |
| | |
---------------------------------------------------------------------------------------------
| | |
| 50 OR 99 | From all the values of this data name, |
| | select only 50 and 99. |
| | |
| | |
---------------------------------------------------------------------------------------------
| | |
| >50 AND <100 | Picks all values between 50 and 100. |
| | |
| | |
---------------------------------------------------------------------------------------------
| | |
| JOE AND MARY | Results in NO RECORD FOUND, since no value |
| | can be both JOE and MARY. |
| | |
| | |
---------------------------------------------------------------------------------------------
| | |
| ^SON AND IL^^ | From all the values of this data name, |
| | select those that end with the letters SON |
| | and those that contain the letters IL in |
| | any position; for example, |
| | |
| | |
| | Bill Johnson |
| | |
| | William Peterson |
| | |
| | |
---------------------------------------------------------------------------------------------
| | |
| "Baker Bill B" | From all the values of this data name, |
| | select only "Baker Bill B". |
| | |
| | NOTE: If there are blanks in the value you |
| | want to select, you must put the value in |
| | quotation marks. |
| | |
| | |
---------------------------------------------------------------------------------------------
| | |
| 20 | From all the values of this data name, |
| | select only those represented by the number |
| | 20; an equal sign is understood. |
| | |
| | |
---------------------------------------------------------------------------------------------
Table 6-1. Examples of Selection Criteria (continued)
---------------------------------------------------------------------------------------------
| | |
| Selection Criteria | Interpretation |
| | |
---------------------------------------------------------------------------------------------
| | |
| LAX OR SFO | From all the values of this data name, |
| | select LAX or SFO only. |
| | |
| | |
---------------------------------------------------------------------------------------------
| | |
| JONES TO SMITH | From all the values of this data name, |
| | select only the names between and including |
| | JONES and SMITH. |
| | |
| | |
---------------------------------------------------------------------------------------------
| | |
| NE 99 | From all the values of this data name, |
| | select only those that are not equal to 99. |
| | |
| | |
---------------------------------------------------------------------------------------------
| | |
| JONES TO SMITH OR BLACK | From all the values of this data name, |
| | select JONES TO SMITH, or select any value |
| | equal to BLACK. |
| | |
| | |
---------------------------------------------------------------------------------------------
| | |
| ^BOB AND BR^ OR JOE | From all the values of this data name, |
| | first select those ending with BOB and |
| | beginning with BR, then select all values |
| | equal to JOE; for example, |
| | |
| | |
| | BROWN BOB |
| | |
| | JOE |
| | |
| | |
---------------------------------------------------------------------------------------------
Data-Name to Data-Name Comparison
INFORM/V also allows you to select data by comparing the value in one
data name with the value in another data name. This specialized kind of
selection criterion is different from that just described in that the
whole specification is done on the Data Names Menu, at the FOR SELECTION
CRITERIA prompt. The values that are used must be numbers representing
the data names available on the Data Names Menu.
Any of the comparison operators listed above for normal selection
criteria can be used in data-name to data-name comparisons, with the
following restrictions:
* For data names that contain character data (type X, U, or 9), the
only operators allowed are =, EQ, <>, and NE. Item lengths do not
have to be the same for the data names being compared.
* For all numeric data types, the types and lengths must be identical.
All operators are valid.
It is not necessarily possible to tell from the data what type has been
specified for the data name. For technical reasons, data names that only
contain numerical data are sometimes identified as one of the character
types. If you have any problems specifying data-name to data-name
comparisons, see your database administrator.
Figure 6-4 shows an example of a typical data-name to data-name
comparison.
_________________________________________________________________________
| |
| INFORM/V HP32246v.uu.ff - (C) Hewlett-Packard Co. 1986 |
| |
| DATA NAMES IN SALES |
| |
| 1: AREA 3: SALES-PERSON 5: YTD-SALES|
| 2: DIST 4: QUOTA |
| |
| TYPE NUMBER(S) FOR DATA NAME(S): |
| |
| TO INCLUDE IN REPORT> 1/5 |
| TO SORT BY> 1,2,3 |
| TO SUBDIVIDE BY> 1 |
| TO SUMMARIZE FOR: AREA> 5 |
| FOR REPORT SUMMARY> |
| FOR SELECTION CRITERIA> 5 > 4 |
| |
| REPORT TITLE> SALES WINNERS |
| |
| PREPARING REPORT |
_________________________________________________________________________
Figure 6-4. Menu Responses for a Data-Name to Data-Name Comparison
In this case, the user wants to see if any of the sales force have yet
gone over their sales quota. On the Data Names Menu, the user responds
to the prompt
FOR SELECTION CRITERIA>
by answering 5 > 4. This response tells INFORM/V to report data only
when YTD-SALES is greater than QUOTA.
Figure 6-5 shows the resulting report. Only one person has so far
exceeded his personal sales quota.
Note that this selection criterion is saved if the report is saved.
Therefore, whenever this report is produced in the future, it will always
report only those sales people who are over quota.
_____________________________________________________________________________________
| |
| SALES WINNERS |
| |
| AREA DIST SALES-PERSON QUOTA YTD-SALES |
| ---------------------------------------------------------------------------- |
| NW CAL BONNANO 145000.00 152000.00 |
| 152000.00*|
| |
| |
| ***** END OF REPORT ***** |
| |
| |
| |
| RETURN TO CURRENT DATA NAMES MENU(Y/N)?> |
| |
| |
| |
| |
_____________________________________________________________________________________
Figure 6-5. The Report Produced by a Data-Name to Data-Name Comparison