You might encounter the issues that are detailed in this note in the following scenarios:

• You install Hot Fix I22107, Hot Fix I22132, and Hot Fix I22134 [SAS^® 9.3M2 (TS1M2)].
• You install Hot Fix V01070 and Hot Fix V01089 [SAS^® 9.4M3 (TS1M3)].
• You install Hot Fix A3Z047 [SAS^® 9.4M4 (TS1M4)].
• You are running SAS^® 9.4M5 (TS1M5)
• You are running SAS^® 9.4M6 (TS1M6)  prior to Rev. 940_19w47
• You are running Viya 3.2, 3.3 or 3.4

Issue #1

A performance issue might occur if the following is true about your SQL procedure query: 

• You are accessing DBMS tables via a LIBNAME engine.
• You include one of the following as part of a WHERE clause:
  • the between operator is used to compare a variable to two constant values
  • the <= and >= operators are used to compare a variable to two constant values
  • the < and > operators are used to compare a variable to two constant values
• The SQL query does not contain any of the following:
  • DISTINCT
  • ORDER BY
  • JOIN of 2 or more tables using the same libref
  • GROUP BY
  • set operator other than OUTER UNION
  • aggregate function
  • HAVING clause
  • WHERE clause containing a subquery that references a DBMS table using the same libref

Issue #2

An Oracle error might occur if the following is true about your PROC SQL query:

• You are accessing Oracle tables via a LIBNAME engine.
• You include one of the following as part of a WHERE clause:
  • the between operator is used to compare a date variable to two constant values
  • the <= and >= operators are used to compare a date variable to two constant values
  • the < and > operators are used to compare a date variable to two constant values
• The table is partitioned by month on the date variable that is being referenced in the WHERE clause.
• The SQL query does not contain any of the following:
  • DISTINCT
  • ORDER BY
  • JOIN of 2 or more tables using the same libref
  • GROUP BY
  • set operator other than OUTER UNION
  • aggregate function
  • HAVING clause
  • WHERE clause containing a subquery that references a DBMS table using the same libref

The Oracle error might look similar to the following:

These issues occur due to an OR condition that is being added to the query submitted to the database in order to support linguistic-sensitive environments. For more information, see SAS Note 54751, "The BETWEEN operator might re-order values, possibly leading to unexpected results in a linguistic-sensitive environment." 

A possible workaround is to specify the variable comparison using the DBCONDITION= data set option.

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

The hot fix addresses queries for non-character variables for issue #1 and date variables for issue #2. 

Operating System and Release Information

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

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

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