DATA Step, Macro, Functions and more

SQL Pass-Through Multiple Connections

Reply
Contributor
Posts: 53

SQL Pass-Through Multiple Connections

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;
Super User
Posts: 5,254

Re: SQL Pass-Through Multiple Connections

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
Data never sleeps
Contributor
Posts: 66

Re: SQL Pass-Through Multiple Connections

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.
Ask a Question
Discussion stats
  • 2 replies
  • 193 views
  • 0 likes
  • 3 in conversation