DATA Step, Macro, Functions and more

Join across different librefs

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

Join across different librefs

Hi,

While joining two different oracle db tables,I got the below error with sastrace on option.But it does not appear without sastrace option.

ERROR: This SQL statement will not be passed to the DBMS for processing because it involves a join across librefs with different connection properties.

To avoid this error,I tried to pull data from oracle tables into sas datasets and then merge but it's taking much more time than joining these two db tables in a single inner join query.Please suggest.Thanks.


Accepted Solutions
Solution
‎03-27-2013 11:28 AM
Super User
Posts: 5,260

Re: Join across different librefs

If you read the note carefully, you'll see that recommend you to re-write your query to exploit explicit SQL pass-thru.

So you need to use Oracle specific syntax to join between the databases.

Data never sleeps

View solution in original post


All Replies
Contributor
Posts: 65

Re: Join across different librefs

Occasional Contributor
Posts: 14

Re: Join across different librefs

Thanks for the reply karl.But as I said in my first post,its taking less time (30 mins) when joining two librefs in single proc sql query.Whereas,while trying to pull data seperately from each database is taking longer than 2hrs.

So I am wondering,how can joining two librefs be inefficient when its taking less time. Since it throws an error with sastrace ON option,its not a showstopper for my coding.Just trying to get more info on this error.

Super Contributor
Posts: 578

Re: Join across different librefs

Its really not an error as much as a warning.  If you were access large datasets across librefs and using sas functions that did not translate to the dbms, sas would pull all of the data down from both tables before processing the query. 

Solution
‎03-27-2013 11:28 AM
Super User
Posts: 5,260

Re: Join across different librefs

If you read the note carefully, you'll see that recommend you to re-write your query to exploit explicit SQL pass-thru.

So you need to use Oracle specific syntax to join between the databases.

Data never sleeps
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 1313 views
  • 6 likes
  • 4 in conversation