topic Re: ERROR: This SQL statement will not be passed to the DBMS for processing because it involves a jo in SAS Programming

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

jozumhannes — Wed, 25 Oct 2023 12:01:06 GMT

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!

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

LinusH — Wed, 25 Oct 2023 12:19:47 GMT

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.

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

jozumhannes — Wed, 25 Oct 2023 12:33:16 GMT

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.

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

jozumhannes — Wed, 25 Oct 2023 12:39:18 GMT

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).

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

LinusH — Wed, 25 Oct 2023 13:58:06 GMT

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.

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

LinusH — Wed, 25 Oct 2023 13:59:13 GMT

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

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

jozumhannes — Wed, 25 Oct 2023 14:19:41 GMT

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.

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

LinusH — Wed, 25 Oct 2023 14:28:18 GMT

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.