DBMS Access Using the SQL Procedure Pass-Through Facility (3)

Accessing Other Vendors' DBMS Data

DBMS Access Using the SQL Procedure Pass-Through Facility

CONNECTION TO Component

To specify the DBMS connection that you want to use, specify the CONNECTION TO component. You can also use the CONNECTION TO component to specify a DBMS connection that you want to create, if you omitted the CONNECT statement in your PROC SQL step.

General form, CONNECTION TO component:
CONNECTION TO dbms-name | alias (DBMS-query)

where

dbms-name | alias identifies the database management system or its alias
(DBMS-query) specifies the SQL query in parentheses.

You specify the CONNECTION TO component in the FROM clause of a PROC SQL SELECT statement:

PROC SQL;
SELECT column-list
FROM CONNECTION TO dbms-name (DBMS-query)
other optional PROC SQL clauses
QUIT;

For example, the following CONNECTION TO component sends an ORACLE SQL query (shown in red) to the ORACLE database for processing. The results from the ORACLE SQL query serve as a virtual table for the PROC SQL FROM clause. In this example, Dblink is a connection alias.

proc sql; connect to oracle as dblink (user=cestmoi pw=trustme path=orapath connection=global); select * from connection to dblink (select id, lastname, firstname, hiredate, salary from employees where salary>35000); disconnect from dblink; quit;

You can use CONNECTION TO in any FROM clause, including those in nested queries.