Passing Joins to Oracle

Before a join across more than one libref can pass to Oracle, all of these components of the LIBNAME statements must match exactly:

• user ID (USER=)
• password (PASSWORD=)
• path (PATH=)

For all queries that use an ON clause, both operands in the ON clause must reference a column name. Literal operands cannot be passed down to the DBMS. Because literal operands are not supported, all ON clauses are transformed into WHERE clauses before passing joins down to the DBMS. This can result in a query not being passed down if it contains additional WHERE clauses or if it contains complex join conditions.

Oracle supports nonstandard outer-join syntax. Outer joins between two or more tables can be passed down to the DBMS with these restrictions:

• Full outer joins are not supported.
• Only a comparison operator is allowed in an ON clause.

For more information about when and how SAS/ACCESS passes joins to the DBMS, see Passing Joins to the DBMS.