ERROR: This SQL statement will not be passed to the DBMS for processing because it involves a join

[jozumhannes]

I'm encountering an error, and I need some assistance. The error message I'm facing is: "ERROR: This SQL statement will not be passed to the DBMS for processing because it involves a join across librefs with different connection properties."

Here's the context: I'm in the process of transferring a table from one library to another using SAS Data Integration. The destination for this data is Snowflake.

I'd appreciate any guidance or insights on resolving this issue. Thank you!

[LinusH]

Is the source data also from Snowflake?

This is what the documention say:

SAS Help Center: Passing Joins to the DBMS

"Passing Joins That Use Two or More LIBNAME Statements

SAS/ACCESS can pass down JOIN operations when more than one LIBNAME statement is involved. In this case, SAS/ACCESS starts by comparing the LIBNAME statements to see whether they are equivalent. Two LIBNAME connections are equivalent when they share these attributes:

• database source types
• connection options, including the user ID, server, and so on

Equivalent LIBNAME connections can connect to different database instances, as long as they are the same type of database and are on the same server. If the LIBNAME statements are determined to be equivalent, then a JOIN operation that uses both LIBNAME connections can be passed to the DBMS.

Note: When you join tables across multiple LIBNAME connections, implicit pass-through uses the first connection to process the data. LIBNAME options from subsequent connections are ignored."

You may get more hints by using these options:

options sastrace=',,,d' sastraceloc=saslog nostsuffix msglevel=i;

So in short, compre the librefs and see if they meet the criteria (probably not...).

The work-around would to do a an explicit SQL pass through.

[jozumhannes]

Thank you for your response.

Yes, both the source and target tables are in the Snowflake database.

Regarding the code: I've already included the following options in my code: options sastrace=',,,d' sastraceloc=saslog nostsuffix msglevel=i;

The interesting part is that, without these options, the job runs and is marked as "successfully completed." However, when I include the code, it results in the error message mentioned above. I'm still investigating this issue and will certainly consider your suggestion of using explicit SQL pass-through. If you have any more insights or recommendations, I'd greatly appreciate them.

[jozumhannes]

Additionally, I've noticed that when I execute the Extract Job using SAS Data Integration Studio, it appears to generate a problem in the Snowflake log. It seems that the job first drops the table and then tries to select from it, which is the opposite of what's specified in the SAS DI Code (where the order makes more sense).

[LinusH]

Can't really help out here without a log to analyse (ideally SAS + SF)

[LinusH]

I don't think it's "real" error message. You you can check the return code after the step to be sure.
But the problem is if the tabels are large a join in SAS probably takes longer time than a correspodning Snowflake join would take.
I don't have any other suggestion at this point than comparing the two librefs and try to make them compatible, so the join could be sent to the Snowflake.

[jozumhannes]

I've noticed that using the complete notation for tables, like "DATABASE"."SCHEMA"."TABLE" (e.g., "SOURCE"."PUBLIC"."TESTTABLE"), seems to be a key factor in resolving the issue.

In this scenario, it appears that when SAS Data Integration Studio automatically includes the database name in the statements, data loading onto Snowflake works without errors. The question now is how to configure SAS Data Integration Studio to consistently use this notation for all table references.

[LinusH]

I don't DI Studio (or SAS for that matters) uses anything else but a two level notation (libref.table).
DI Studio might do it if you use the explicit pass through option (availble in Join).
And SAS/ACCESS might produce threel level anme in the engine itself.
Agian, any code/logs would help understanding what your are facing.