Connecting Strings to Order Conditions

The order in which SYSTEM 2000 processes conditions can affect which data records are selected. This is most obvious when you include a SYSTEM 2000 where-clause in a view descriptor, and specify a WHERE clause in a SAS program that uses the view descriptor. By default, the interface view engine connects the translated WHERE clause conditions in SAS to the end of the SYSTEM 2000 where-clause conditions by using the Boolean operator AND.

To affect the order of the connected conditions, you can include a connecting string in a SYSTEM 2000 where-clause to tell the engine how you want to connect the conditions. See the following table:

The following table summarizes the connecting strings that you can specify in a SYSTEM 2000 where-clause that is included in a view descriptor.

You can specify a connecting string in a SYSTEM 2000 where-clause after a keyword or a special character. For example,

   C1 = A AND *SAS*

The following syntax is not acceptable:

    C1 = A *ANDSAS*

however, you can use the preceding syntax if you include a delimiter (special character.) In the following example, the delimiter is a set of single quotation marks:

    C1 = 'A' *ANDSAS*

If a view descriptor includes a SYSTEM 2000 where-clause with a connecting string, and you do not execute a WHERE clause in SAS, there is nothing to substitute. For example, suppose you have included the following SYSTEM 2000 where-clause (with the connecting string *SAS*) in a view descriptor:

    C1 = A AND *SAS*

Then, you issue a SAS program specifying a WHERE clause that produces the following SYSTEM 2000 condition:

    C110 > 27

If you do not specify a WHERE clause in the SAS program, the "dangling connector" would result in a SYSTEM 2000 error.

    C1 = A AND

If you want the flexibility of omitting the WHERE clause in SAS, you can use the *ANDSAS* or *SASAND* connecting string. For example,

    C1 = 'A' *ANDSAS*

Then, even if you did not specify a WHERE clause in SAS, there would not be a problem. The result would be:

    C1 = 'A'

You cannot use an OR operator to connect a connecting string to other parts of a view descriptor where-clause. For example, the following view descriptor where-clauses are not acceptable:

    C1 = A OR *SAS*
    C1 = C OR (C1 = A OR C1 = B) *ANDSAS*

However, you can use the OR operator as shown in the following example:

    (C1 = A OR C1 = B) AND *SAS*

The HAS and AT operators and the NON-KEY specification are available in a SYSTEM 2000 where-clause, but they are not available in a WHERE clause in SAS. By using specific connecting strings, you can make the function of HAS, AT, and NON-KEY more useful in the SYSTEM 2000 where-clause and have the option of omitting the WHERE clause in SAS without introducing errors or unexpected results. See the following table for examples.