ACCESS Procedure Reference

WHERE Clause in a View Descriptor

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.

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 aliases 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.

field-name<(occurrence)>|key-name

is the CA-Datacom/DB name of the field or key for which you are specifying criteria. The field must be selected in the view descriptor. The interface view engine assumes that the name in a condition is a SAS name. If it is not, the name will be treated as a CA-Datacom/DB name.

If the field is a repeating field, you must specify the occurrence of that field in parenthesis, where occurrence is one of the following:

indicates the nth occurrence. For example,

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.

operator

is one of the following:

= 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

range-operator

is one of the following:

= or EQ or SPANS	is within the range (inclusive)
!= or ¬ = or NE	is outside the range

value, high-value, and low-value

represent valid values for the field or key.

For more information, see Specifying Values in View WHERE Clauses.

The Asterisk in View WHERE Clauses

The asterisk (*) is required when comparing two field names. For example, the following WHERE clause selects those records where the wages are less than the commission:

where ytd-wages*<ytd-commission*

This WHERE clause

where ship-quant*=order-quantity*

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 in View WHERE Clauses for details on the use of quotation marks.

If the asterisk appears in a value, enclose the value in quotation marks or use the DDBSPANS system option to specify another special character. For more information about system options, see System Options for the CA-Datacom/DB Interface.

View WHERE Clause Expressions

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 by 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 2x10⁶:

where part=9846 & on-hand>2.Oe+6

Specifying Values in View WHERE Clauses

Character Fields in View WHERE Clauses

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,

where lastname=Smith

extracts data for SMITH, and the next example extracts data for Smith:

where lastname='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

where name=Anderson

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 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.

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 quotation marks, the two consecutive single quotation marks are treated as one quotation mark. You can also use double quotation marks around a value. Also, two consecutive double quotation marks become one double quotation mark if surrounded by double quotation marks. If two consecutive double quotation marks are surrounded by single quotation marks, they remain two double quotation marks and conversely.

Date Values in View WHERE Clauses

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 software.

$HEX. Format Fields in View WHERE Clauses

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,

where code=f1f

extracts the data for CODE equals 10 (F1F0).

Values That Do Not Fit the Field Picture

If you specify a value that does not fit the field's picture, you might receive an error, or the value might 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.

Various Misfit Field Values
Condition	Request Sent to CA-DATACOM/DB
cost=.003	Error	(underflow: field has two decimals)
cost>.003	cost>0.00	(truncated)
cost>3.0052	cost>3	(truncated)
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.

Field Values That Are Too Large
Condition	Request Sent to CA-DATACOM/DB
cost<11123	cost 999.99
cost ¬= 9999	Error (overflow, field cannot store integers > 999)
cost >= -12345	cost - 999.99

Masking Values in View WHERE Clauses

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 about system options, see System Options for the CA-Datacom/DB Interface.

Multi-Field Keys in View WHERE Clauses

For a condition that specifies a multi-field key, you might need to enclose each value with delimiters.

Note: You cannot use compound fields in the WHERE clause. [cautionend]

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

where init-id=\jde\27#\

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

where init-id=jde27#####

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

where weight-sex=78m

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

where age-degree=25bs

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 satisfy it. [cautionend]

Guidelines for View WHERE Clauses

Consider the following guidelines when you specify a WHERE clause in the view descriptor:

You can enter a WHERE clause or a SORT clause or both, in either order. But if you enter both, do not use a terminator between them.
The keyword WHERE is not required unless the WHERE clause is the second clause (following the SORT clause). The SORT clause must begin with SORT.
CA-Datacom/DB does not have a date data type. However, the selection criteria will honor a SAS date format if you specify one in the CONTENT and INFORMAT statements.
The CA-Datacom/DB fields must be selected in the view descriptor in order for you to use them in the WHERE clause.
All conditions in the WHERE clause must refer to fields in a single table. To join conditions that pertain to two CA-Datacom/DB tables, use the SQL procedure.
If you enter a SAS WHERE clause when you use the view descriptor in a SAS procedure, the SAS WHERE clause is connected to the WHERE clause in the view descriptor (if any) with the AND operator.
The WHERE clause is not parsed (or checked) until the interface view engine tries to execute it for a procedure.
Field names in the WHERE clause conditions can be SAS names or CA-Datacom/DB names. However, you should use SAS names for repeating fields or for fields within repeating fields.
Character literals and values for zoned decimal fields can contain the pound sign (#) to indicate masking out characters for pattern matching operations.

For more information about specifying WHERE clauses, see Deciding How to Specify Selection Criteria in CA-Datacom/DB.

FOOTNOTE 1: Use the DDBDELIM system option to change the default delimiter character (\). For more information about system options, see System Options for the CA-Datacom/DB Interface.

Top of Page