topic Re: SQL Pass-Through Multiple Connections in SAS Programming

SQL Pass-Through Multiple Connections

mdavidson — Thu, 07 May 2009 14:33:05 GMT

Hello,

I'm new to pass-through and am trying to connect across two databases using an alias for each connection. This is probably a simple solution, but how can I join the below query by CUST_ID across the two connections?

PROC SQL OUTOBS=20;
CONNECT TO ORACLE AS CON1 (USER=UID PASSWORD=PASS PATH=DB1 CONNECTION=GLOBAL);
CONNECT TO ORACLE AS CON2 (USER=UID PASSWORD=PASS PATH=DB2 CONNECTION=GLOBAL);
CREATE TABLE WORK.TEST AS
SELECT * FROM CONNECTION TO CON1 (SELECT CUST_ID FROM CUSTOMERS);
SELECT * FROM CONNECTION TO CON2 (SELECT CUST_ID,CUST_NAME FROM CUSTOMER_DETAIL);
DISCONNECT FROM CON1;
DISCONNECT FROM CON2;
QUIT;

Re: SQL Pass-Through Multiple Connections

LinusH — Fri, 08 May 2009 09:27:14 GMT

The easiest way is to use LIBNAMEs to the Oracle databases, and then just join them in a simple proc sql. Most preferable would be to have the join performed within Oracle, but I know too little about Oracle to tell how this could be enforced.

/Linus

Re: SQL Pass-Through Multiple Connections

CameronLawson — Sat, 09 May 2009 00:20:22 GMT

I believe Oracle supports sql into multiple db's at once. Off memory you reference it via @ against the table. That should allow you to reference two db's from the one connection. A Google Search should give you the exact syntax.