The LIBNAME Statement for Relational Databases OR_UPD_NOWHERE= LIBNAME Option 109
into a single result set. For example if an IN clause contained 4,000 values, Oracle will
produce 4 in clauses that each contain 1,000 values. A single result will be produced, as
if all 4,000 values were processed as a whole.
The OLE DB DBMS restricts the number of values allowed in an IN clause to 255.
Setting DBKEY= automatically overrides MULTI_DATASRC_OPT=.
DIRECT_SQL= can impact this option as well. If DIRECT_SQL=NONE or
NOWHERE, the IN clause cannot be built and passed to the DBMS, regardless of the
value of MULTI_DATASRC_OPT=. These setting for DIRECT_SQL= prevent a WHERE
clause from being passed.
Examples
The following example will build and pass an IN clause from the SAS table to the
DBMS table, retrieving only the necessary data to process the join:
proc sql;
create view work.v as
select tab2.deptno, tab2.dname from
work.sastable tab1, dblib.table2 tab2
where tab12.deptno = tab2.deptno
using libname dblib oracle user=testuser password=testpass
multi_datasrc_opt=inclause;
quit;
The query is passed to the DBMS, generating an IN clause to select only the necessary
columns and rows.
The following example prevents the building and passing of the IN clause to the
DBMS, requiring all rows from the DBMS table to be brought into SAS for processing
the join:
libname dblib oracle user=testuser password=testpass multi-_datasrc_opt=none;
proc sql;
select tab2.deptno, tab2.dname from
work.table1 tab1,
dblib.table2 tab2
where tab1.deptno=tab2.deptno;
quit;
See Also
“DBMASTER= Data Set Option” on page 180
OR_UPD_NOWHERE= LIBNAME Option
Specifies whether SAS uses an extra WHERE clause when updating rows with no locking
Valid in: the SAS/ACCESS LIBNAME statement
DBMS support: Oracle
Default value: YES
