When you join a SAS^® table to a database management system (DBMS) table on a single column, the join might be converted to a WHERE clause that contains an IN condition, and a list of values from the SAS table is passed to the DBMS so that the query resolves in the DBMS.

However, under some circumstances, if the SELECT clause contains a CASE expression and an ALIAS, the list of values might not be passed. In this case, the DBMS table is pulled back to SAS, resulting in poor performance.

Here is an example.

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

proc sql; 
   create table work.table as 
          select oracle_table.ora_var1, 
                 case when oracle_table.ora_var2="x" then "" 
                    else oracle_table.ora_var2 
                 end as newvar 
                 from work.sas_table 
                      inner join dbms.oracle_table on sas_table.sas_lookup=oracle_table.ora_lookup;
quit;

Here, SASTRACE= shows that a SELECT * was executed:

ORACLE_10: Prepared: on connection 0
   SELECT * FROM mySchema.ORACLE_TABLE
ORACLE_13: Executed: on connection 0
   SELECT statement  ORACLE_10

To work around the issue, remove the AS alias NEWVAR from the original query. SASTRACE shows that a WHERE clause is passed with the IN condition:

ORACLE_18: Prepared: on connection 0
   SELECT  "ORA_LOOKUP", "ORA_VAR1", "ORA_VAR2" FROM mySchema.ORACLE_TABLE  WHERE  ( ("ORA_LOOKUP" IN  ( '1' , '2' , '3' ) ) )
ORACLE_19: Executed: on connection 0
   SELECT statement  ORACLE_18

Click the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

A fix for this issue for Base SAS 9.4_M2 is available at:

A fix for this issue for Base SAS 9.3_M2 is available at: