BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jshaik
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

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

4 REPLIES 4
jshaik
Calcite | Level 5

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.

DBailey
Lapis Lazuli | Level 10

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. 

LinusH
Tourmaline | Level 20

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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