36 When Passing Joins to the DBMS Will Fail Chapter 5
However, there are several reasons why a join statement under PROC SQL might
not be passed to the DBMS for processing. In general, the success of the join depends
upon the nature of the SQL that was coded and the DBMS’s acceptance of the
generated syntax. It is also greatly influenced by the use of option settings. The
following are the primary reasons why join statements might fail to be passed:
The generated SQL syntax is not accepted by the DBMS.
PROC SQL attempts to pass the SQL join query directly to the DBMS for
processing. The DBMS can reject the syntax for any number of reasons. In this
event, PROC SQL attempts to open both databases individually and perform the
join internally.
The SQL query involves multiple librefs (LIBNAME statements) that do not share
connection characteristics.
If the librefs are specified using different servers, user IDs, or any other
connection options, PROC SQL will not attempt to pass the statement to the
DBMS for direct processing.
The use of data set options in the query.
The specification of any data set option on a table that is referenced in the SQL
query will prohibit the statement from successfully passing to the DBMS for direct
processing.
The use of certain LIBNAME options.
The specification of LIBNAME options that request member level controls, such
as table locks (“READ_LOCK_TYPE= Data Set Option” on page 206 or
“UPDATE_LOCK_TYPE= Data Set Option” on page 214), will prohibit the
statement from successfully passing to the DBMS for direct processing.
The “DIRECT_SQL= LIBNAME Option” on page 99 option setting.
The DIRECT_SQL= option default setting is YES. PROC SQL attempts to pass
SQL joins directly to the DBMS for processing. Other setting for the
DIRECT_SQL= option influence the nature of the SQL statements that PROC
SQL will attempt to pass down to the DBMS, or even if it will attempt to pass
anything at all.
DIRECT_SQL=YES
PROC SQL automatically attempts to pass the SQL join query to the DBMS.
This is the default setting for this option. The join attempt could fail due to a
DBMS return code. If this happens, PROC SQL attempts to open both data
sets individually and perform the join internally.
DIRECT_SQL=NO
PROC SQL does not attempt to pass SQL join queries to the DBMS. Other
SQL statements can be passed, however. If the “MULTI_DATASRC_OPT=
LIBNAME Option” on page 108 is in effect, the generated SQL can also be
passed.
DIRECT_SQL=NONE
PROC SQL does not attempt to pass any SQL directly to the DBMS for
processing.
DIRECT_SQL=NOWHERE
PROC SQL attempts to pass SQL to the DBMS including SQL joins. However,
it does not pass any WHERE clauses associated with the SQL statement.
This causes any join that is attempted with direct processing to fail.
DIRECT_SQL=NOFUNCTIONS
PROC SQL does not pass any statements in which any function is present to
the DBMS. Normally PROC SQL attempts to pass down any functions coded
in the SQL to the DBMS, provided the DBMS supports the given function.
