SQL Pass-Through Multiple Connections

[mdavidson]

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;

[LinusH]

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

[CameronLawson]

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.