When Passing Joins to the DBMS Fails

There are several reasons why a join under PROC SQL might not be passed to the DBMS for processing. In general, the success of the join depends on 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 values. Here are the primary reasons why join statements might fail to be passed.

The DBMS does not accept the generated SQL syntax.

PROC SQL attempts to pass an 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 tables individually and perform the join in SAS.

Each DBMS has specific limitations about what it allows in a join. Here are some situations that are not supported in all databases:

• Mixing an outer join with an inner join
• Performing a full outer join
• Using prohibited operators in an ON clause or using a literal operand in an ON clause.
• Using a WHERE clause in addition to an ON clause

For the specific criteria that apply to your DBMS, see the information about passing joins for your SAS/ACCESS interface.

The SQL query involves multiple librefs that do not share connection characteristics.

If the librefs are specified using different data sources (DBMS engines), servers, user IDs, or any other connection options, PROC SQL does not attempt to pass the statement to the DBMS for direct processing.

Using data set options in the query

Specifying most data set options on a table that is referenced in an SQL query prohibits SAS from successfully passing the statement to the DBMS for direct processing.

Note: The PRE_STMT_OPTS=, POST_STMT_OPTS=, PRE_TABLE_OPTS=, and POST_TABLE_OPTS= data set options are exceptions, because SAS passes the arguments for these options down to the DBMS for processing.

Using certain LIBNAME options

Specifying LIBNAME options that request such member-level controls as table locks (READ_LOCK_TYPE= or UPDATE_LOCK_TYPE=) prohibits the statement from successfully passing to the DBMS for direct processing.

Using SAS functions on the SELECT clause

Specifying SAS functions on the SELECT clause can prevent joins from being passed.