|SAS/ACCESS Interface to CA-DATACOM/DB: Reference|
You can use a WHERE Clause in a view descriptor to select specific records from a CA-DATACOM/DB table. You can reference any CA-DATACOM/DB field included in the view descriptor.
|View WHERE Clause Syntax|
A WHERE clause in a view descriptor consists of the word WHERE followed by one or more conditions that specify criteria for selecting records from one CA-DATACOM/DB table. (WITH and WH are valid synonyms for the word WHERE.)
A condition can be one of the following:
field-name<(occurrence)>|key-name operator value field-name* operator field-name* field-name<(occurrence)>|key-name range-operator low-value * high-value
The user-supplied elements of the WHERE clause conditions are described here.
If the field is a repeating field, you must specify the occurrence of that field in parenthesis, where occurrence is one of the following:
|n||indicates the nth occurrence. For
where address(3) contains dallas
selects those records where the third occurrence of ADDRESS contains DALLAS.
|ALL||indicates all occurrences selected
in the view descriptor. For example, the WHERE clause below selects those
records where all occurrences of ADDRESS contains DALLAS.
where address(all) contains dallas
|ANY||indicates any occurrence. An asterisk
(*) can be used instead of ANY. For example,
where address(any) contains dallas
selects those records where any occurrence of ADDRESS contain DALLAS. You could have used ADDRESS(*) instead.
|= or EQ||equal to|
|> or GT||greater than|
|< or LT||less than|
|!= or ¬ = or NE||not equal|
|>= or GE or GTE||greater than or equal to|
|<= or LE or LTE||less than or equal to|
|CONTAINS or CONTAINING||contains|
|¬ CONTAIN or ¬ CONTAINING||does not contain|
|!CONTAIN or !CONTAINING||does not contain|
|= or EQ or SPANS||is within the range (inclusive)|
|!= or ¬ = or NE||is outside the range|
For more information, see Specifying Values in WHERE Clauses.
|View WHERE Clause Examples|
The asterisk (*) is required when comparing two field-names. For example, the folowing WHERE clause selects those records where the wages are less than the commission:
This WHERE clause
selects those records where the ship-quantity is equal to the order-quantity.
The asterisk is also required when comparing low and high range values. For example, the following WHERE clause selects employees with employee numbers between 2300 and 2400:
where number spans 2300*2400
The WHERE clause
where lastname spans 'A'*'Smith'
selects those employees with last names up to Smith. See Character Fields for details on the use of quotes.
If the asterisk appears in a value, enclose the value in quotes or use the DDBSPANS system option to specify another special character. For more information on system options, see System Options.
Conditions can be combined 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.
where cost=.50 & (type=ansi12 | class=sorry)
Conditions can also be preceded with NOT (X).
where cost=.50 & not (type=ansi12 | class=sorry)
The following WHERE clause selects all records where AVAIL is Y or W:
where avail eq y | avail eq w
The next WHERE clause selects all records where PART is 9846 and ON-HAND is greater than 2x106:
where part=9846 & on-hand>2.Oe+6
|Specifying Values in WHERE Clauses|
The next few pages discuss guidelines and considerations
that govern how you specify values in WHERE clause conditions.
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,
extracts data for SMITH, and the next example extracts data for Smith:
If the value is shorter than the field, it is padded on the right with blanks before the comparison. (No padding is done if you use the CONTAINS operator.) If the value is longer than the field, it is truncated to the field length before the comparison is done. The WHERE clause
selects all records where NAME is ANDERSON. The WHERE clause
where city='TRUTH OR CONSEQUENCES' | stzip='NM 87901'
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.
In this example, either of these WHERE clauses
where shop='Joe''s Garage' where shop="Joe;s Garage"
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 quote. You can also use double quotes around a value. Also, two consecutive
double quotes become one double quote if surrounded by double quotes. If two
consecutive double quotes are surrounded by single quotes, they remain two
double quotes and vice versa.
You can use the DB Content
statement to specify a date format. Using this statement, you can specify
the dates according to your SAS informat. Do not use 'd as you would for SAS
For fields that are converted to $HEX. format because of their data type or length (see ACCESS Procedure Data Conversions), the value must be specified in hexadecimal. A value longer than the field is truncated to the field length before the comparison is done. A value shorter than the field is padded on the right with binary zeros before the comparison. For example, if CODE has $HEX4. format,
extracts the data for CODE equals 10 (F1F0).
If you specify a value that does not fit the field's picture, you may receive an error, or the value may be adjusted to fit the picture before sending the request to CA-DATACOM/DB.
The following examples illustrate how various misfit values are handled. Assume throughout that COST has a database length of 5, with 2 decimals.
In the first set of examples, some misfit values produce errors, some are truncated, and some cause operators to be changed. Errors occur when the equals operator or not equals operator is used with a misfit value. Operators are changed when that change plus truncation means the value will fit the picture and still produce the results you intended.
|Condition||Request Sent to CA-DATACOM/DB|
|cost=.003||Error||(underflow: field has two decimals)|
|cost<.0001||cost 0.00||(truncated, < changed to )|
|cost<20.001||cost 20||(truncated, < changed to )|
The next examples show values that exceed the field size. If possible, your values are replaced with the largest value that can be stored in the field.
|Condition||Request Sent to CA-DATACOM/DB|
|cost ¬= 9999||Error (overflow, field cannot store integers > 999)|
|cost >= -12345||cost - 999.99|
When a condition includes the EQ, NE, CONTAINS, or NOT CONTAINS operator and the field is in display code, you can mask the value. That is, you can specify that only certain positions within the value are to be compared to those positions in the field. A pound sign (#) marks the positions that you do not want to be compared. For example,
where zipcode eq 7#8
selects all records with zip codes that have a 7 in the first position and an 8 in the third position. The condition
where lastname contains m#n
selects all records with last names such as Mendoza, Harman, and Warminsky.
If you use the EQ or NE operators and you mask a value that is shorter than the database field, your values are padded on the right with mask characters. (No padding is done for NOT CONTAINS.) For example,
where lastname eq m#n
would select records with last names such as Mendoza, McNeal, and Monroe. Names such as Harman or Warminsky would not qualify.
Use the DDBMASK system option to change the default
masking character (#). For more information
on system options, see System Options.
For a condition that specifies a multi-field key, you may need to enclose each value with delimiters.
Note: You cannot use compound fields in the WHERE clause.
For multi-key fields, use a delimiter character(footnote 1) before and after each value if the value you are entering is not the same length as the multi-field key and you are using either NOT CONTAINS or the mask character. Values for keys are always in display code. For example, suppose INIT-ID is a multi-key field. INIT is a character field of length 3, and ID is a numeric field of length 7. The WHERE clause
selects all records where the initials are JDE and the ID number starts with 27. Your value for ID is padded on the right with mask characters, so the entire value is treated as if you had specified JDE27#####.
You can omit delimiters if you specify the same number of characters as the multi-field key contains. For example, this WHERE clause
also selects all records where the initials are JDE and the ID number starts with 27, just as in the previous example. No delimiters are required here because JDE27##### is 10 characters long, which is the same size as the key field.
When you do not include delimiters or masked characters in the value, blanks or zeros are used for padding. The WHERE clause
selects all records where weight equals 78 and sex equals M. The value is treated as if it had been specified as \78\m\.
On the other hand, the WHERE clause
selects all records where age equals 25 and degree equals BS. The value is treated as if it had been specified as \25\bs \.
Note: A considerable amount of processing is required
when a procedure must convert an apparently simple condition into a complex
request to CA-DATACOM/DB. For example,
if the fields AGE and SEX are not contiguous within the record, the procedure
converts the condition AGE-SEX<25M to SEX<M OR (SEX=M AND AGE<25)
before submitting the request. CA-DATACOM/DB,
in turn, processes the request and, if possible, uses permanent indexes to
Consider the following guidelines when you specify a WHERE clause in the view descriptor:
For more information on specifying WHERE clauses, see Deciding How to Specify Selection Criteria.
Top of Page
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.