Chapter Contents |
Previous |
Next |
SAS/ACCESS Interface to ADABAS Software |
Multiple-Value Fields in WHERE Clauses 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 |
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 Output with ADABAS Multiple-Value Field Name in View WHERE Clause.
Output with 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 the SAS System 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. The SAS System post-processes all records returned from the interface view engine to see if they meet the SAS WHERE clause in effect.
where branch_1='LONDON'The SAS WHERE clause produces the results in Output with the Individual Occurrence SAS Variable in SAS WHERE Clause.
Output with the 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 Output with _ANY Variable in View or SAS WHERE Clause are produced. They are the same as for Output with ADABAS Multiple-Value Field Name in View WHERE Clause.
Output with _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
where branch_1='LONDON' or branch_2='LONDON' or branch_3='LONDON' ...
Chapter Contents |
Previous |
Next |
Top of Page |
Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.