ACCESS Procedure Reference

WHERE Clause in an ADABAS View Descriptor

You can use a WHERE clause in a view descriptor to select specific ADABAS records.

A view WHERE clause consists of the SUBSET and WHERE (or WH) keywords, followed by one or more conditions that specify criteria for selecting records. A condition has one of the following forms:

field-name<(occurrence)> operator value
field-name<(occurrence)> range-operator
   low-value * high-value

The user-supplied elements of the WHERE clause conditions are described below:

field-name

is the ADABAS name of the data field or corresponding SAS variable name for which you are specifying criteria. This data field must be selected in the view descriptor. (The procedure will assume that any name in a condition is a SAS name. If it is not, the procedure will treat it as an ADABAS name.) If the field's ADABAS name is not unique within a NATURAL DDM, you must specify its external name.

A referenced data field must be an ADABAS descriptor field in the following situations:

The view WHERE clause contains more than one condition.
The view WHERE clause uses the SPANS or NE operator.
You are also specifying a view SORT clause.
You are also planning to issue a SAS BY statement or a SAS ORDER BY clause in a SAS program that references a view descriptor containing a view WHERE clause.
You are also planning to issue a SAS WHERE clause in a SAS program that references a view descriptor containing a view WHERE clause.

(occurrence)

is a numeric value from 1 to 99 identifying the nth occurrence of a periodic group. You must use parentheses around the number. This is an optional value. If you do not specify an occurrence number, all occurrences are selected.

operator

can be one of the following comparison and logical operators:

= or EQ	equal to
> or GT	greater than
< or LT	less than
!= or ¬= or NE	not equal to
or GE or GTE	greater than or equal to
or LE or LTE	less than or equal to

range-operator

can be one of the following operators:

= or EQ or SPANS

within the range (inclusive)

value or high-value or low-value

is a valid value for the data field.

View WHERE Clause Examples

This section gives brief examples using the WHERE clause and explains what each example does.

Specifying Conditions with the SPANS Operator

When comparing low and high values, the asterisk is required. For example, the following WHERE clause selects those employees with employee numbers between 2300 and 2400:

subset where personnel-number spans 2300 * 2400

The following WHERE clause selects those employees with last names up through Smith:

subset where name spans 'A' * 'Smith'

Specifying Expressions

You can combine conditions to form expressions. Two conditions can be joined with OR (|) or AND (&). Since expressions within parentheses are processed before those outside, use parentheses to have the OR processed before the AND.

subset where cost = .50 & (type = ansi12 | 

class = sorry)

The following WHERE clause selects all records where AVAIL is Y or W:

subset where avail eq y | avail eq w

The next WHERE clause selects all records where PART is 9846 and ON-HAND is greater than 1,000:

subset where part = 9846 & on-hand > 1000

Specifying Values in Character Fields

For character fields, you can use quoted or unquoted strings. Any value entered within quotation marks is left as is; all unquoted values are uppercased and redundant blanks are removed. For example, the following clause extracts data for SMITH:

subset where lastname = Smith

The next example extracts data for Smith:

subset where lastname = 'Smith'

The next WHERE clause selects all records where CITY is TRUTH OR CONSEQUENCES or STZIP is NM 87901. Notice in the first condition that quotation marks prevent OR from being used as an operator. In the second condition, they prevent the extra space between NM and 87901 from being removed.

subset where city = 'TRUTH OR CONSEQUENCES' | 

stzip = 'NM  87901'

The following example selects all records where SHOP is Joe's Garage. Because the value is enclosed in quotation marks, the two consecutive single quotation marks are treated as one.

subset where shop = 'Joe''s Garage'

You can also use double quotation marks, for example,

subset where shop = "Joe's Garage"

Specifying Numeric Format Values

For numeric values, use decimal or scientific notation. For example,

subset where horsepower = 2.5

Specifying Dates

Numeric values representing dates in an ADABAS file are not automatically converted to SAS date values. They are simply treated as numbers. For example, 103098 is considered less than 113188.

However, the ACCESS procedure provides you the ability to specify a SAS date format with the CONTENT statement. Then, numeric values are converted to SAS dates. To reference them in a view WHERE clause, use informat representation (without the 'D at the end as in SAS). See CONTENT Statement for more information about specifying a SAS date format with the CONTENT statement.

Specifying Values in Superdescriptor Fields

A superdescriptor field is treated as if it has an alphanumeric (character) ADABAS standard format unless all of the parent fields from which it is derived have a binary (numeric) format.

When you enter a value for a numeric superdescriptor or an alphanumeric superdescriptor where one or more of its parent fields have a numeric format, the value must be in character hexadecimal format because many data types and from-to specifications can be contained in one superdescriptor value. When you enter a value for a character superdescriptor, the value must be entered as character data.

Note: By assigning a SAS format of HEXw. to superdescriptors that are derived from one or more numeric fields in a view descriptor, you can see the internal hexadecimal values. You can then use these values as a guide for entering like values in the WHERE clause. [cautionend]

For example, the NATURAL DDM named CUSTOMERS has the character superdescriptor field STATE-ZIPLAST2, which is defined as

'SP=ST(1,2),ZI(1,2)'

The two data fields that make up STATE-ZIPLAST2 are defined as

DDM Name    ADABAS ID    ADABAS TYPE    LENGTH
--------    ---------    -----------    ------
STATE          ST             A           2
ZIPCODE        ZI             U           5

If you want to select the value TX from the data field STATE and the value 78701 from the data field ZIPCODE, the view WHERE clause would be as follows:

subset where state_zi = E3E7F0F1

The comparable SAS WHERE clause would be

where state_zi = 'E3E7F0F1'x

F0F1 is the hexadecimal internal representation of a positive zoned decimal value of 01. If ZIPCODE were defined as packed and the from-to specification were the same, the hexadecimal representation 001F would represent the value 01. Similarly, 0001 would be the correct representation for either binary or fixed. A sign (+ or -) must also be entered according to type and ADABAS requirements.

Suppose you want to access a character superdescriptor field named DEPT-PERSON, which is defined as

'S2=DP(1,6),LN(1,18)'

The two data fields that make up DEPT-PERSON are defined as

DDM Name    ADABAS ID    ADABAS TYPE    LENGTH
--------    ---------    -----------    ------
  DEPT         DP             A           6
  LASTNAME     LN             A           18

If you want to select the value TECH01 from the data field DEPT and the value BOYER from the data field LASTNAME, the view WHERE clause would be as follows. (Note that unquoted values in the view WHERE clause are uppercased.)

subset where dept-person = tech01boyer

A comparable SAS WHERE clause would be

where dept-person = 'TECH01BOYER'

Specifying Values in Subdescriptor Fields

Subdescriptors take the ADABAS type of their parent and the length of their from-to specification. Unlike superdescriptors, subdescriptor values consist of only one data type.

For example, the NATURAL DDM named CUSTOMERS has the numeric subdescriptor field ZIPLAST, which is defined as

'SB=ZI(1,2)'

The data field that ZIPLAST is based on is defined as

DDM Name    ADABAS ID    ADABAS TYPE    LENGTH
--------    ---------    -----------    ------
ZIPCODE        ZI             U           5

If you want to select the values 78701, 82701, and 48301, the view WHERE clause and the SAS WHERE clause would be as follows.

View WHERE clause:

subset where ziplast2 = 01

SAS WHERE clause:

where ziplast2 = 01

Now suppose you want to access a character subdescriptor field named DEPT-CODE, which is defined as

'DC=DP(1,4)'

The data field that DEPT-CODE is based on is defined as

DDM Name    ADABAS ID    ADABAS TYPE    LENGTH
--------    ---------    -----------    ------
  DEPT         DP             A           6

If you want to select the values TECH01 , TECH04 , and TECH23 , the view WHERE clause would be

subset where dept-code = tech

The comparable SAS WHERE clause would be

where dept-code = 'TECH'

Specifying Values in Multiple-Value Fields

If the field name refers to a multiple-value field, all values for the field are compared with the value that you specify. For example, if CARD is a multiple-value field, the following view WHERE clause selects all records where any one of the values of CARD is VISA.

subset where card eq visa

Note that in a SAS WHERE clause, you cannot specify a value for a multiple-value field; however, in a SAS WHERE clause, you can specify an occurrence, which you cannot do in a view WHERE clause.

For more information about and examples of using multiple-value fields in selection criteria, see Using Multiple-Value Fields in Selection Criteria.

Specifying Values in Periodic Group Fields

If the field is in a periodic group, use field-name(occurrence) to identify the field in the nth occurrence of the group. For example, the following WHERE clause selects all records where PHONE is 234-9876 in the second occurrence of the periodic group containing PHONE.

subset where phone(2) eq 234-9876

Note that the 2 after PHONE refers to the second occurrence of its parent periodic group and not to the second occurrence of PHONE.

If you do not specify an occurrence number, all occurrences are checked. For example, the following WHERE clause selects all records where PHONE is 234-9876 in any occurrence of the periodic group containing PHONE.

subset where phone eq 234-9876

For more information about and examples of using periodic group fields in selection criteria, see Using Multiple-Value Fields in Selection Criteria.

Top of Page