Advanced Topics |
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 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 |
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.
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
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
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' ...
Copyright © 2007 by SAS Institute Inc., Cary, NC, USA. All rights reserved.