WHERE Clause in an ADABAS View Descriptor

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'

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

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:

subset where shop = "Joe's Garage"

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

subset where horsepower = 2.5

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.

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.

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

'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:

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

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

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'

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'

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 Multiple-Value Fields in Selection Criteria.

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 Periodic Group Fields in Selection Criteria.