where-clause in SYSTEM 2000

Using the where-clause (SYSTEM 2000)

where-clause Syntax (SYSTEM 2000)

where-clause Examples (SYSTEM 2000)

Combining Conditions with AND (&) and OR ( | )

Qualifying Unmatched Conditions with NOT (¬)

Designating-specific Types of Records with HAS

Specifying Position in Database with AT

Processing Order

Using the where-clause (SYSTEM 2000)

A SYSTEM 2000 where-clause is used to select specific logical entries in a SYSTEM 2000 database. If the password that you are using has where-clause authority for each selected item, you might select any item included in the access descriptor from which the view descriptor is derived.

When you include a SYSTEM 2000 where-clause in a view descriptor, the selection criteria are executed each time you use the view descriptor in a SAS program. When a SYSTEM 2000 where-clause is invoked, the interface view engine:

replaces selections of SAS variable names with database item component numbers. (The SAS variable names must correspond to a database item included in the view descriptor.)
translates keywords to uppercase for compatibility with SYSTEM 2000.
expands connecting strings to connect the WHERE clause in SAS to the where-clause in the view.
preserves significant blanks in delimited text values.

The syntax of the where-clause can include one or more of the following conditions. However, you cannot include a Collect File item name or the SAME operator in a where-clause that is included in a view descriptor.

Note: This is a partial description of the SYSTEM 2000 where-clause. For a complete description, see SYSTEM 2000 Software: QUEST Language and System-Wide Commands, Version 12, First Edition.

where-clause Syntax (SYSTEM 2000)

WHERE expression;

WHERE

is the keyword that designates a where-clause. This keyword is optional if the where-clause is the first clause or if you do not specify an ordering-clause. WH is an alias.

expression might be one of the following:

condition
(expression)
NOT expression
expression AND expression
expression OR expression
record HAS expression
expression AT n

condition [NON-KEY] item might be one of the following:

unaryoperator
binaryoperator value
ternaryoperator value * value
CONTAINS text
* binaryoperator item*

NON-KEY

enables you to change a KEY condition to a NON-KEY condition. This capability is not available in a WHERE clause in SAS. For information about using connecting strings to extend the function of the NON-KEY specification to the WHERE clause conditions in SAS, see Using HAS, AT, and NON-KEY. NK is an alias for NON-KEY.

NOT

finds the complement of specified criteria. You can also use a logical not (¬) symbol.

AND

combines two expressions by finding data records that satisfy both expressions. You can also use an ampersand (&).

combines two expressions by finding data records that satisfy either expression or both. You can also use a vertical bar ( | ).

record

is a schema record name or component number.

HAS

specifies a data record by its position under its parent. This capability is not available in a WHERE clause in SAS. For information about using connecting strings to extend the function of the AT operator to the WHERE clause conditions in SAS, see Using HAS, AT, and NON-KEY.

is 0 or a positive integer that indicates position of a record under its parent. The last position is indicated by 0.

item

is a schema item name or component number included in the access descriptor. You can specify a SAS variable name if the item is included in the view descriptor. The item can be KEY or NON-KEY.

unary operators: EXISTS (EXIST, EXISTING) | FAILS (FAIL, FAILING)

specifies the existence or non-existence of values.

binary operators: EQ, NE, GE, GT, LE, or LT

compares an item with a value or compares two items. You can also use the symbols shown in Table 7.2.

Binary Operators and Equivalent Symbols
Operator	Equivalent Symbol
EQ	=
NE	¬= or !=
GE	>= or => or ¬< or !<
GT	>
LE	<= or =< or ¬> or !>
LT	<

ternary operators: EQ, NE, or SPANS (SPAN, SPANNING)

compares an item with a range of values. Ternary operators require a low value and a high value. You can also use the symbols shown in Table 7.3. There is no equivalent symbol for SPANS.

Ternary Operators and Equivalent Symbols
Operator	Equivalent Symbol
EQ	=
NE	¬= or !=

value

is a literal value or the SYSTEM 2000 system string *TODAY*. You can enclose a value with a delimiter of your choice. As shown in the following example, sometimes you might need delimiters around character values to preserve a mixed-case value. Any special character that appears at the beginning and end of a character value is assumed to be a delimiter.

where c1 = 'Abc De' looks for Abc De 
where c1 = @Abc De@ looks for Abc De 
where c1 = @Abc De  looks for @Abc De

CONTAINS (CONT, CONTAIN, CONTAINING)

searches for characters within an item's values.

text

for the syntax and explanation of CONTAINS text, see SYSTEM 2000 Software: QUEST Language and System-Wide Commands, Version 12, First Edition.

where-clause Examples (SYSTEM 2000)

Unary Operators

Unary operators search for values that exist or do not exist using the EXISTS and FAILS operators. SYSTEM 2000 unary operators are similar to SAS missing values expressions.

The following where-clause qualifies data records that have a value for the item ACCRUED VACATION.

      where accrued vacation exists

The following where-clause qualifies data records that do not have a value (that is, nulls) for the item ACCRUED VACATION.

      where accrued vacation fails

Binary Operators

Binary operators compare items with a value or compare two items by using the EQ, NE, GT, GE, LT, or LE operators (or their equivalent symbols).

The following where-clause qualifies data records that have the value for EMPLOYEE NUMBER equal to 1224.

     where employee number=1224

The following where-clause qualifies data records where EMPLOYEE STATUS is not equal to FULL TIME. (However, it does not qualify those records where EMPLOYEE STATUS is null.)

     where employee status ne full time

The following where-clause qualifies data records where the value for HIRE DATE is greater than or equal to June 1, 1987.

     where hire date=>06/01/1987

The following where-clause qualifies data records where the value for C105 equals the value for C4.

     where C4 * EQ C105 *

Ternary Operators

Ternary operators search for values in a range of values by using the SPANS, EQ, and NE operators (or their equivalent symbols).

The following where-clause qualifies data records where BIRTHDAY spans the dates January 1, l949 and January 31, 1949, inclusively.

     where birthday spans 01/01/1949 * 01/31/1949

CONTAINS Operator

The CONTAINS operator searches for values that contain patterns of characters within values.

The item must be a CHARACTER, TEXT, or UNDEFINED item.

The following where-clause qualifies data records where the values for STREET ADDRESS contain the character string RIM ROCK.

     where street address contains /RIM ROCK/

Combining Conditions with AND (&) and OR ( | )

Using the AND and OR operators, you can combine two or more conditions. AND combines two conditions by selecting values that satisfy both conditions. OR combines two conditions by selecting values that satisfy either or both conditions.

The following where-clause qualifies data records that have COBOL in the item SKILL TYPE and 4 in the item YEARS OF EXPERIENCE.

   where skill type=cobol & years of experience=4

Qualifying Unmatched Conditions with NOT (¬)

Using the NOT operator, you can select data records where values do not match a condition.

The following where-clause selects data records for the item PAY SCHEDULE that do not equal the value HOURLY or that are null.

    where ¬pay schedule=hourly

Designating-specific Types of Records with HAS

Using the HAS operator, you can specify a focal record.

In the following where-clause, the HAS operators specify C0 (the ENTRY record) as the focal record because both conditions refer to the same schema record (C201). In this example, the HAS operators qualify C0 records that have the values COBOL and Fortran for C201. (If the HAS operator is not used, no records would qualify because there would never be a C201 value of both COBOL and Fortran.)

where C0 has c201 eq cobol and C0 has c201 eq fortran

Specifying Position in Database with AT

Using the AT operator, you can select values that are stored in a specified position in the database. Values must satisfy the condition and occupy a specific position. A data record's position is its number (reading left-to-right) below its parent record.

The following where-clause qualifies the data record in position 2 in a logical entry.

     where position title eq programmer at 2

Processing Order

The order in which SYSTEM 2000 processes conditions can affect which data records are selected. SYSTEM 2000 processes conditions that have operators in the following order: AT, HAS, NOT, AND, OR.

When conditions are joined by the same operator, SYSTEM 2000 first processes KEY conditions (conditions that are indexed) from right-to-left, then NON-KEY conditions (ones not indexed) from right-to-left.

You can alter the processing order by changing the order of the conditions and by enclosing conditions in parenthesis. Conditions enclosed in parenthesis are processed first.

Because the software processes the AND operator before the OR operator, in order to access the names of employees who have an MBA degree and either a major or minor in Marketing, use the following where-clause:

where degree=mba & 
 (major field=marketing | minor field=marketing)

The following where-clause would also result in SYSTEM 2000 selecting the names of employees who have a minor in Marketing and degrees other than MBAs.

where degree=mba & 
 major field=marketing | minor field=marketing