Multiple-Value Fields in Selection Criteria

Using Multiple-Value Fields in Selection Criteria

A multiple-value field can have 0 to 191 values per record, and ADABAS assigns an occurrence number to each value. When you include a multiple-value field in SAS/ACCESS descriptor files, you can use SAS variables that reference individual occurrences and a SAS variable that references all occurrences to perform special WHERE clause queries.

The following table lists whether you can use a multiple-value field or its corresponding SAS variables in the SAS and view WHERE clauses.

Multiple-Value Fields in WHERE Clauses

Multiple-Value Field	SAS WHERE Clause	View WHERE Clause
ADABAS data field name	no	yes
SAS name for individual Occurrence variable	yes	no
_ANY variable	yes	yes

Multiple-Value Fields WHERE Clause Examples

Introduction to Multiple-Value Fields WHERE Clause Examples

Using the multiple-value data field BRANCH-OFFICE from the CUSTOMERS DDM, the following examples illustrate using a multiple-value field in WHERE clauses.

Multiple-Value Fields WHERE Clause Example 1

In a view WHERE clause, you can reference an ADABAS multiple-value field name, but you cannot do so in a SAS WHERE clause. For example, with the following WHERE clause in a view descriptor, the interface view engine searches all values of the multiple-value field:

where branch-office='LONDON'

The view WHERE clause produces the results in the following output.

Results of ADABAS Multiple-Value Field Name in View WHERE Clause

OBS    CUSTNUM     BR_ANY      BRANCH_1    BRANCH_2    BRANCH_3    BRANCH_4
  1    14324742                TORONTO     HOUSTON     TOKYO       LONDON
  2    26422096                LONDON      NEW YORK
  3    26984578                LONDON      NEW YORK    ROME
  4    27654351                LONDON      BOSTON
  5    28710427                LONDON

Multiple-Value Fields WHERE Clause Example 2

You can use the individual occurrence SAS variables created by the ACCESS procedure such as BRANCH_1, BRANCH_2, and so on, in SAS WHERE clauses, but you cannot use them in a view WHERE clause. Note that individual occurrence conditions must be processed by SAS after ADABAS has completed its selection processing.

For example, the following SAS WHERE clause searches the second occurrence for BRANCH-OFFICE and retrieves the London values. SAS post-processes all records returned from the interface view engine to see whether they meet the SAS WHERE clause in effect.

where branch_1='LONDON'

The SAS WHERE clause produces the results in the following output.

Results of Individual Occurrence SAS Variable in SAS WHERE Clause

OBS    CUSTNUM     BR_ANY      BRANCH_1    BRANCH_2    BRANCH_3    BRANCH_4
  1    26422096                LONDON      NEW YORK
  2    26984578                LONDON      NEW YORK    ROME
  3    27654351                LONDON      BOSTON
  4    28710427                LONDON

Multiple-Value Fields WHERE Clause Example 3

You can use the _ANY variable created by the ACCESS procedure in both a SAS WHERE clause and a view WHERE clause. However, if you use the _ANY variable in a SAS WHERE clause, the ADABAS interface view engine must be able to process the entire SAS WHERE clause.

For example, with the following WHERE clause, the engine searches all occurrences of the multiple-value field:

where br_any = 'LONDON'

Whether that WHERE clause is a SAS WHERE clause or a view WHERE clause, the results in the following output are produced. They are the same as for Results of ADABAS Multiple-Value Field Name in View WHERE Clause .

Results of _ANY Variable in View or SAS WHERE Clause

OBS    CUSTNUM     BR_ANY      BRANCH_1    BRANCH_2    BRANCH_3    BRANCH_4
  1    14324742                TORONTO     HOUSTON     TOKYO       LONDON
  2    26422096                LONDON      NEW YORK
  3    26984578                LONDON      NEW YORK    ROME
  4    27654351                LONDON      BOSTON
  5    28710427                LONDON

This functionality prevents you from having to enter repetitive selection criteria such as the following:

where branch_1='LONDON' or branch_2='LONDON' 
      or branch_3='LONDON' ...