|SAS/ACCESS Interface to ADABAS Software|
You can use a WHERE clause in a view descriptor to select specific ADABAS records.
|View WHERE Clause Syntax|
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:
A referenced data field must be an ADABAS descriptor field in the following situations:
|= 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|
|= or EQ or SPANS||within the range (inclusive)|
|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'
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 quotes 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 = SmithThe 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 quotes 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 quotes, the two consecutive single quotes are treated as one.
subset where shop = 'Joe''s Garage'You can also use double quotes, 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
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 the SAS System). See CONTENT for more information on 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.
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 5If you want to select the value
TXfrom 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 = E3E7F0F1The 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 18If you want to select the value
TECH01from the data field DEPT and the value
BOYERfrom 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 = tech01boyerA 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 5If 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 6If you want to select the values
TECH23, the view WHERE clause would be
subset where dept-code = techThe 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 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 and examples of using periodic group fields in selection criteria, see Using Multiple-Value Fields in Selection Criteria.
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.