HLI: Find criteria for Model 204 data: Difference between revisions

Latest revision as of 00:39, 16 July 2016

Overview

This topic describes how to specify find criteria to select data from the Model 204 database for application programmers who are using the Host Language Interface facility.

See HLI: Function summary for a description of HLI function calls.

See Understanding retrieval conditions for complete information about selecting data.

Find criteria in HLI calls

An HLI application program specifies find criteria in calls that perform find functions. To select data from the Model 204 database, specify find criteria using any of the following HLI calls on an IFSTRT thread. On an IFDIAL thread you can also specify find criteria using the equivalent SOUL command, which is listed beside each HLI call:

HLI call	Equivalent SOUL command
IFFAC	FIND AND PRINT COUNT
IFFDV	FIND ALL VALUES
IFFIND	FIND
IFFNDX	FIND EXCLUSIVE
IFFWOL	FIND WITHOUT LOCKS

A find specification can include a combination of conditions that might require a mix of index and direct searches against the database. See File search operations for more information about index and direct searches.

Model 204 selects records in order by one of the following storage schemes, based on the file type:

In record number sequence, for records in an entry order or hashed file
In sort key sequence, for records in a sorted file
In a particular collating sequence, for records in an ordered file

See HLI: Model 204 files and records for more information about different types of data files.

Specifying all records to be selected

An HLI application program can select all records in the current file or group by omitting criteria in the find specification. To specify all records to be selected, use the following entry in the find specification in the HLI call:

;END;

Specifying particular records to be selected

To select a particular group of records from a file of group for processing, an HLI application program must specify find criteria. A single HLI call can include multiple conditions for selecting data in a find specification.

Specify find criteria using either:

Values, to select fields that contain numeric and character values
Patterns, to select a field value based on a character string pattern

Model 204 performs two basic kinds of find criteria comparisons, numeric and character, based on the type of data that is stored in a file.

See Understanding retrieval conditions for complete information about find criteria and examples of find specifications.

File search operations

Each Model 204 file contains a data area and an index area. The data area, Table B, contains the data records. The index area contains references to the data records organized by key field and value.

Index search

When Model 204 locates records stored in the data area using the index area, this is called an index search.

Usually, Model 204 searches the index, either the hash index (Tables C and D) or the Ordered index (Table D, that is, the B-tree), for fields having any of the following types of attributes:

KEY
ORDERED
NUMERIC RANGE

When storing fields that have these attributes, Model 204 makes special entries in the index. For certain types of find criteria using values, Model 204 directly accesses the appropriate index entry to find which records satisfy the find criteria without searching through the records in the data area of the file.

Whether or not Model 204 uses the index depends on the type of find criteria specified. See File search based on field attributes and find criteria for a summary of file search operations for fields having KEY, ORDERED, and NUMERIC RANGE attributes. Note that, in general, find criteria based on KEY, ORDERED, or NUMERIC RANGE fields are extremely fast and efficient.

Direct data search

In contrast to find criteria that involve an index search, the following types of find conditions result in a direct search of the data in the Table B data area:

Inequality character conditions
IS PRESENT condition
Conditions that specify fields having any of the following types attributes:
- NONKEY
- NONORDERED
- NONRANGE

Note: A direct search can have a significantly adverse effect on performance if the search involves a large number of records. However, the cost can be greatly reduced in some cases where both index and direct find criteria are specified in the same HLI call.

When processing index and direct find criteria for the same HLI call, Model 204 reduces the number of records to be searched directly by performing the index selection first. Model 204 does not search directly records that are either selected or eliminated based on the index search.

File search for a group

When selecting data from a file group, Model 204 first determines which records from the individual member files meet the selection specifications. This yields a set of records that might contain entries from all files.

Model 204 then selects the individual records in order by file.

Summary of file search operations

The following table summarizes file search operations for an HLI call that finds records. The type of file search depends on field attributes and on find criteria specified in the call.

In the following table, the search operations correspond to find criteria specified in the following HLI calls: IFFIND, IFFAC, IFFWOL, and IFFNDX. The find criteria listed in the table, that is, an equality, a character range, a numeric range, and a character and numeric range combination, specify value criteria.

See Specifying find criteria: character values for a description of find specifications that specify value criteria. See HLI: Model 204 fields and variables for information about field attributes.

See Record retrievals for information about optimizing file search operations for find specifications.

File search based on field attributes and find criteria
Type of Find Criteria	Field Attribute
	Key	Numeric range	Ordered		Key and Ordered		Other
	Key	Numeric range	Number	Character	Number	Character	Other
Equality	Index1	Index1	Index2	Index2	Index1	Index1	Data
Character range	Data	Data	Data	Index2	Data	Index2	Data
Numeric range	Data	Index1	Index2	Data'	Index2	Data	Data
Character and numeric range	Data	Data	Data'	Index2	Data	Index2	Data

Data = Data area search in Table B
Index1 = Hash index search in Table C and, if needed, in Table D
Index2 = Ordered index search in Table D (B-tree)

Specifying find criteria: character values

You can specify selection criteria that tests the values of a field to determine whether a record with a particular value, either character or numeric, is selected.

You can specify any of the following types of find criteria using values:

Equality (using an equal sign)
Character range
Numeric range
Character and numeric range combination

The following sections give basic guidelines for use, general syntax, and examples of value find criteria. comparison operators summarizes the operators that are valid for use in find specifications with value criteria.

Specifying find criteria using character values

With character find criteria you can test a field that contains character string values to locate records that have a particular value or range of values. The comparison uses the EBCDIC collating sequence.

Specify character find criteria using any of the following formats:

field name = value field name = NOT value fieldname {IS | IS NOT} [ALPHABETICALLY] {BEFORE | AFTER | operator} value

where operator specifies one of the range operators in Comparison operators.

You can specify character find criteria for a KEY or NONKEY field. Note, however, that selection of KEY fields is substantially more efficient than selection of NONKEY fields.

See Record retrievals for more information about character values used in find specifications.

Character find criteria with an equality condition

The following examples illustrate character value find criteria that specify an equality condition.

In the following example, the field name is TOWN and the value is CAMBRIDGE. The following find criterion specifies employees who live in CAMBRIDGE:

TOWN=CAMBRIDGE;END;

Alternatively, the following find criterion specifies employees who do not live in CAMBRIDGE:

TOWN=NOT CAMBRIDGE;END;

Character find criteria with a range condition

The following examples illustrate character value find criteria that specify a range condition:

In the following example, the field is NAME and the value is SMITH. The following find criterion specifies names that precede the value SMITH in EBCDIC collating sequence:
NAME IS BEFORE SMITH;END;
In this example, the BEFORE SMITH specification selects records that contain the value NAME=SMALL but does not select records that contain the value NAME=SMITHIE.
In the following example, the field is NAME. The following find criterion specifies names that do not follow the value WALKER in EBCDIC collating sequence:
NAME IS NOT AFTER WALKER;END;
In the following example, the field is NAME. The following find criterion specifies names that do not precede the value JOHNSTON in EBCDIC collating sequence:
NAME IS NOT ALPHA LT JOHNSTON;END;
In the following example, the field is NAME. The following find criterion specifies names that follow THORNE and precede THYME (such as, THULE) in EBCDIC collating sequence:
NAME IS BEFORE THYME AND AFTER THORNE;END;
In the following example, the field is NAME. The following find criterion specifies a negative relation and selects names equal to and preceding THORNE and equal to and following THYME in EBCDIC collating sequence:
NAME IS NOT BEFORE THYME AND AFTER THORNE;END;

Specifying find criteria: numeric values

With numeric find criteria you can test a field for numerical values less than, greater than, or equal to a particular value. You can specify negative numbers.

You can specify numeric find criteria for fields that have any of the following attributes:

ORDERED NUMERIC
NUMERIC RANGE
NONRANGE

Specify numeric find criteria using either of the following formats:

fieldname = value fieldname {IS | IS NOT} [NUMERICALLY] [operator] value

where operator specifies one of the range operators in Comparison operators.

Rules for specifying numeric range find criteria

The following rules apply for specifying numeric range find criteria:

When you specify IS, Model 204 selects records in which the content of the numerical field is algebraically equivalent to the specified constant. Note that this differs from specifying the equal sign (=), which results in a search for an exact character match.
A negated comparison operator (IS NOT) for numeric find criteria produces a different result for a NUMERIC RANGE field and a NONRANGE or ORDERED field.
See Field attributes and negated numeric find criteria for differences in find criteria for fields having different attributes.
If a numeric range find is performed on a field that is defined with the NUMERIC RANGE or ORDERED NUMERIC attribute, Model 204 finds only those records where the numerical fields contain the following characters:
- Optional leading plus (+) or minus (-) sign.
- Digits 0-9 and an optional decimal point.
- No more than 10 digits on either side of the decimal point, and a maximum of 20 digits, for NUMERIC RANGE fields.
- No more than 10 digits on either side of the decimal point for ORDERED NUMERIC fields, and only the first 15 significant digits are used.
See Fieldname=value pairs for numeric find criteria for examples of specifications that contain the required characters for NUMERIC RANGE and ORDERED NUMERIC fields.

See Record retrievals for more information about numeric find criteria.

Numeric find criteria with an equality condition

The following examples illustrate numeric value find criteria that specify an equality condition.

There are differences using an equal sign (=) and the word IS in a numeric specification. For example, the following find criterion selects only records that contain a value of 37 in the field WEIGHT:

WEIGHT=37;END;

Alternatively, the following find criterion selects records that contain values of 37, 0037, 37.0, for WEIGHT, and that might include any other string whose numerical value is 37:

WEIGHT IS 37;END;

Fieldname=value pairs for numeric find criteria

The following examples show valid fieldname=value pairs for fields defined with NUMERIC RANGE or ORDERED NUMERIC attributes:

PRICE = 3317 AGE = 013 VOLUME = 517.6473 WAVE LENGTH = +.0072 TEMPERATURE = -21

See Specifying find criteria: numeric values for information about valid characters for NUMERIC RANGE and ORDERED NUMERIC field values.

Numeric find criteria with a range condition

The following examples illustrate numeric value find criteria that specify a range condition:

In the following example, the field is SETTLEMENT DATE. The following find criterion specifies dates that are less than the specified value 800305 (that is, before March 5, 1980):
SETTLEMENT DATE IS < 800305
In the following example, the field is AGE. The following find criterion specifies ages that are greater than the specified value 20:
AGE IS NUM AFTER 20
In this example, NUM (NUMERICALLY) specifies that a numeric type comparison be performed using the operator AFTER, instead of the default, which is character comparison.
In the following example, the field is AGE. The following find criterion specifies records in which age is numerically less than 21:
AGE IS LESS THAN 21;END;
In the following example, the field is AGE. The following find criterion specifies records in which age is numerically equal to or greater than 21 (a NUMERIC RANGE field attribute is assumed):
AGE IS NOT LESS THAN 21;END;
In the following example, the field is AGE. The following find criterion specifies records in which age is numerically greater than 21:
AGE IS GREATER THAN 21;END;
In the following example, the field is TEMPERATURE. The following find criterion specifies records in which the value of temperature is numerically less than minus 10 (degrees):
TEMPERATURE IS LESS THAN -10;END;
In the following example, the field is AGE. The following find criterion specifies ages that are numerically greater than 21 and less than 25:
AGE IS BETWEEN 21 AND 25;END;
In the following example, the field is AGE. The following find criterion specifies ages that are numerically less than or equal to 21 and greater than or equal to 25 (a NUMERIC RANGE field attribute is assumed):
AGE IS NOT BETWEEN 21 AND 25;END;

Field attributes and negated numeric find criteria

For numeric find criteria that specify a negated condition, the set of records selected depends on whether the field is defined with the NUMERIC RANGE, ORDERED, or NONRANGE attribute.

In the following example, the field is AGE. The following negated find criterion specifies records in which age is not less than 21:

AGE IS NOT LESS THAN 21;END;

Using this example, Model 204 selects records differently based on the field attributes defined for AGE, as follows:

If AGE is defined as NUMERIC RANGE, this statement selects records containing AGE fields whose values are numerical and greater than or equal to 21.
If AGE is defined as NONRANGE or NONORDERED, this statement selects records that do not contain AGE fields less than 21. However, the selected records include those that contain nonnumerical AGE fields and those that contain no AGE field.
If AGE is defined as NONRANGE and INVISIBLE, this statement does not select any records.

Specifying value find criteria using an IN RANGE clause

You can specify beginning and ending values in find criteria or combine conditions in find criteria by using the IN RANGE clause.

Use the following form of the IFFIND, IFFAC, IFFNDX, and IFFWOL call to specify a beginning and ending range of values:

fieldname {IS | IS NOT} [NUMERICALLY | ALPHABETICALLY] IN RANGE [FROM | AFTER] value1 {TO [AND] BEFORE} value2

Note the following uses for the IN RANGE clause:

The IN RANGE clause is particularly useful when a beginning and ending range of values must be specified for a multiply occurring field.
If you use the IN RANGE clause for ORDERED fields, the find can be optimized because the search on the Ordered Index is restricted between two values.

See IN RANGE clause for more information.

Defining a numeric value in exponent notation

You can define a numeric value in a find specification in exponential notation.

Exponential notation has the following format:

[ + | - ]{whole_number | whole_number.fractional_number | .fractional_number} E[ + | - ]exponent

where:

A maximum of 15 significant digits is allowed for number values. If the number of significant digits exceeds 15, the remaining precision is lost.
The exponent expression must be between 75 and -75.
Embedded spaces are not allowed in the exponent string.

The following examples are of valid numeric values expressed in exponent notation:

-1322.444E14 15E-47 +99233.0332E-66 222E+11

For more information, see IFFTCH and IFGET. See HLI: Field formatting options for HLI calls for more information about conversion of exponential numbers.

See Exponent notation for more information.

Specifying find criteria: special conditions

You can specify any of the following types of special conditions in find specifications in HLI calls that find records (that is, an IFFIND, IFFAC, IFFWOL, and IFFNDX):

IS PRESENT condition, which selects only records that contain at least one occurrence of the specified field regardless of its value.
In the following example, the specification locates all records that contain one or more occurrences of the field CHILD:

CHILD IS PRESENT;END;

Alternatively, in the following example, the specification locates all records that do not contain a ZIP CODE field:

ZIP CODE IS NOT PRESENT;END;
FIND$ condition, which selects a set of records previously selected.
SFL$ condition, which selects all records for which the sort field is less than the stated value.
SFGE$ condition, which selects all records for which the sort field is greater than or equal to the stated value.
In the following example, from a file sorted by last name, the specification selects the records for all males starting at JONES:

SFGE$ JONES;SEX=MALE;END;

And, in the following example, the specification locates records of all last names starting with the letter K:

SFGE$ K;SFL$ K999;END;
POINT$ condition, which selects all records in a file that contain internal record numbers greater than or equal to a specified value. (POINT$ is not valid in group context.)
In the following example, the statement selects all records in which AGE is greater than 20, and in which record numbers range from 288 to 5000 inclusive:

AGE IS GREATER THAN 20;POINT$ 288 AND NOT POINT$ 5001;END;
FILE$ condition, to restrict files from which records meeting the specified criteria are selected.
In the following example, if the program has opened group REGION, which contains files MARYLAND, VIRGINIA, and DELAWARE, the specification finds all males in the group who do not live in Delaware:

SEX=MALE;NOT FILE$ DELAWARE;END;

In the following example, if the program has opened group REGION, which contains files MARYLAND, VIRGINIA, and DELAWARE, the specification finds all males in the group who live in Delaware or Virginia:

SEX=MALE;FILE$ DELAWARE OR VIRGINIA;END;
LIST$ condition, which finds records that are on a list.
In the following example, to find all males on list A1, the find specification is:

SEX=MALE;LIST$ A1;END;

See Record retrievals for complete information about the special find conditions.

Using comparison operators

The following table summarizes the comparison operators that are valid for use in find specifications with character and numeric values.

Except for AFTER and BEFORE, which default to character-string comparison in EBCDIC sequence, the operators in the table default to numeric comparison.

The table does not show the IN RANGE clause, which defaults to numeric comparison, and the IS PRESENT condition, which defaults to character-string comparison.

Comparison Operators
IS operator	Abbreviation	Symbol	The field value must be...
`Numeric comparison`
EQUAL	EQ	=	Equal to the value specified.
NOT EQUAL	NE	¬=	Unequal to the value specified.
LESS THAN	LT	<	Less than the value specified.
LESS THAN OR EQUAL TO	LE	<=	Less than or equal to the value specified.
GREATER THAN	GT	>	Greater than the value specified.
GREATER THAN OR EQUAL TO	GE	>=	Greater than or equal to the value specified.
BETWEEN value1 AND value2	--	--	Between value1 and value2. If the field value is equal to value1 or value2, Model 204 does not select the record. Note: If value1 is greater than value2, Model 204 does not select any records.
`Character-string comparison (EBCDIC collating sequence)`
AFTER	--	--	After the specified value or string
BEFORE	--	--	Before the specified value or string

Operator and value type mismatch

The following results occur when an operator and value type are not matched:

If a specification uses a numeric operator with a nonnumerical value, Model 204 does not select any records. The following example illustrates this type of mismatch:
LAST NAME IS GREATER THAN ANDREWS
If a specification uses a numeric operator with a nonnumerical value and is negated, Model 204 selects every record in the file or group. The following example illustrates this type of mismatch:
LAST NAME IS NOT GREATER THAN ANDREWS
If a specification uses a character string operator with a numeric value, Model 204 converts all numeric values in the field to character strings and performs a character-by-character comparison of field values to the find criteria.
The following example illustrates this type of mismatch:

YEAR IS BEFORE 1986

In this example, numbers such as 942, 700, or 2 would not be selected.

Note: To avoid unexpected results when using an operator that defaults to numeric comparison in character value find criteria, specify ALPHA (ALPHABETICALLY).

Interpretation of values used in find criteria

During data selection, Model 204 interprets numbers, strings, and exponential notation in value find criteria. Model 204 determines how to interpret values based on the type of find criteria (equality or range) and operators that the condition specifies.

Model 204 performs either a character or numeric comparison. The following sections describe the comparisons that Model 204 performs for equality and range conditions specified in find criteria. See Comparison operators for a list of operators and default comparisons.

Equality conditions in find criteria

For an equality condition in a find specification (having the form fieldname = value), Model 204 interprets the value differently depending on whether or not the field is defined with the FLOAT field attribute.

Model 204 performs comparisons for equality find criteria based on the following rules:

If the field is defined with the FLOAT attribute, Model 204 examines the value to determine whether it is a number, exponent notation, or a character string.
Depending on the value type, Model 204 performs one of the following comparisons:
- Numeric comparison, if the value is a number or in exponent notation.
- Character string comparison, if the value is a character string.
If the field is not defined with the FLOAT attribute, Model 204 treats the value as a string and performs a character string comparison.
Note: For a non-FLOAT field, Model 204 does not convert exponent notation to a numerical form. For example, if a field that is not defined with the FLOAT attribute contains a value of .1234E-3, the comparison .1234E-3 = .0001234 is not true.

Range conditions in find criteria

For either of the following types of range find criteria, Model 204 interprets the value differently depending on whether or not the condition specifies the NUMERICALLY or ALPHABETICALLY keyword:

Having the form:
fieldname IS operator value
Using the IN RANGE clause

Depending on the keyword, Model 204 performs comparisons for range find criteria based on the following rules:

If the condition specifies the NUM (NUMERICALLY) keyword, Model 204 interprets the value as a number and performs a numerical comparison.
If the condition specifies the ALPHA (ALPHABETICALLY) keyword, Model 204 interprets the value as a string and performs a character string comparison.
If the condition does not specify either the NUMERICALLY nor ALPHABETICALLY keyword, Model 204 interprets the value and performs the comparison according to the default comparison type of the operator. See Comparison operators for a list of operators and default comparisons.

Using Boolean operators

The following guidelines apply for using Boolean operators to specify find criteria:

Find criteria can combine several Boolean operators as well as character and numeric comparison operators. You can use AND, OR, NOT, NOR, and parentheses to combine conditions in find criteria.
Interpretation of a Boolean expression proceeds from left to right, except where the order of precedence dictates otherwise.
An operation of higher precedence following an operation of lower precedence is performed first.
Expressions enclosed inside parentheses are evaluated first. You can nest parentheses, in which case evaluation proceeds from the innermost to the outermost set.

The following table shows the order of precedence that Model 204 follows when evaluating multiple Boolean operations in a find criterion.

The following examples of find specifications with Boolean operators show the basic form of these specifications. See Boolean operators in retrieval statements for more information about Boolean operators.

Order of precedence for Boolean operators
Order	Operator
1st	NOT
2nd	NOR
3rd	AND
4th	OR
5th	AND (implied by a new line)

Using Boolean operators to combine conditions

The following examples illustrate the basic form of a find specification that uses Boolean operators to combine conditions:

In the following example, the field name is TOWN. The following find criterion specifies employees who live in CAMBRIDGE and in CHICAGO:
TOWN=CAMBRIDGE OR TOWN=CHICAGO;END;
The following specification shows how to modify the above example and produce the same results; the second TOWN= can be omitted:
TOWN=CAMBRIDGE OR CHICAGO;END;
In the following example, the field name is TOWN. The following find criterion specifies all employees except those who live in CAMBRIDGE, CHICAGO, and NEWPORT:
TOWN=NOT CAMBRIDGE NOR CHICAGO NOR NEWPORT;END;
In the following example, the field name is SKILL (in a personnel file). The following find criterion specifies employees who have a certain job skill combination, that is, typing and proficiency in the French language:
SKILL=TYPING AND FRENCH;END;
In the following example, the field name is SKILL. The following find criterion specifies employees who have a certain job skill (steno) and not another (typing):
SKILL=STENO AND NOT TYPING;END;

Note: You can also combine find criteria using several Boolean operators as well as comparison operators for character and numeric values. See Boolean operators in retrieval statements for additional examples of find criteria using Boolean operators.

Specifying find criteria: pattern matching

You can specify find criteria using a field value that is in the form of a pattern. Specify patterns using the find calls: IFFIND, IFFAC, IFFWOL, IFFNDX, or IFFDV.

Using pattern find criteria, Model 204 does not process records for which the field specified in the find criteria is not present. For a character string find specification on a multiply occurring field, Model 204 selects all records with at least one occurrence of the field that meets the specified condition.

Note: Model 204 evaluates the pattern for each value in the specified field and selects those values that match the criteria in the pattern. Selected values must match patterns character by character, including blanks, except when special characters are used.

Specify a pattern-matching condition using the following format:

fieldname [IS | IS NOT] LIKE "pattern"