DIRECT_SQL= LIBNAME Statement Option

Lets you specify whether generated SQL is passed to the data source for processing.

Valid in:	LIBNAME statement
Default:	YES
Supports:	All

Syntax

Details

Examples

Example 1: Prevent a DBMS from Processing a Join

Example 2: Prevent a DBMS from Processing a SAS Function

Syntax

| NOMULTOUTJOINS

Syntax Description

YES: specifies that generated SQL from PROC SQL is passed directly to the data source for processing.

NO: specifies that generated SQL from PROC SQL is not passed to the data source for processing. This is the same as specifying the value NOGENSQL.

NONE: specifies that generated SQL is not passed to the data source for processing. This includes SQL that is generated from PROC SQL, SAS functions that can be converted into DBMS functions, joins, and WHERE clauses.

NOGENSQL: prevents PROC SQL from generating SQL to be passed to the DBMS for processing.

NOWHERE: prevents WHERE clauses from being passed to the data source for processing. This includes SAS WHERE clauses and PROC SQL generated or PROC SQL specified WHERE clauses.

NOFUNCTIONS: prevents SQL statements from being passed to the data source for processing when they contain functions.

NOMULTOUTJOINS: specifies that PROC SQL will not attempt to pass any multiple outer joins to the data source for processing. Other join statements might be passed down however, including portions of a multiple outer join.

Details

By default, processing is passed to the data source when possible, because the data source might be able to process the functionality more efficiently than SAS does. In some instances, however, you might not want the data source to process the SQL. For example, the presence of null values in DBMS data might cause different results depending on whether the processing takes place in SAS or in the DBMS. If you do not want the data source to handle the SQL, use DIRECT_SQL= to force SAS to handle some or all of the SQL processing.

If you specify DIRECT_SQL=NOGENSQL, PROC SQL does not generate data source SQL. This means that SAS functions, joins, and DISTINCT processing that occur within PROC SQL are not passed to the DBMS for processing. (SAS functions outside PROC SQL can still be passed to the DBMS.) However, if PROC SQL contains a WHERE clause, the WHERE clause is passed to the data source, if possible. Unless you specify DIRECT_SQL=NOWHERE, SAS attempts to pass all WHERE clauses to the data source.

If you specify more than one value for this option, separate the values with spaces and enclose the list of values in parentheses. For example, you could specify DIRECT_SQL=(NOFUNCTIONS, NOWHERE).

Examples

Example 1: Prevent a DBMS from Processing a Join

The following example prevents a join between two tables from being processed by the DBMS, by setting DIRECT_SQL=NOGENSQL. Instead, SAS processes the join.

proc sql;
   create view work.v as
      select tab1.deptno, dname from
         mydblib.table1 tab1,
         mydblib.table2 tab2
   where tab1.deptno=tab2.deptno
   using libname mydblib fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
      dsn=oradsn direct_sql=nogensql;

Example 2: Prevent a DBMS from Processing a SAS Function

The following example prevents a SAS function from being processed by the DBMS.

libname mydblib fedsvr server="d1234.us.company.com" 
   port=2171 user=user1 pwd=pass1
   dsn=oradsn direct_sql=nofunctions;
proc print data=mydblib.tab1;
   where lastname=soundex ('Paul');
run;