Problem Note 50951: Running a query with the > and < operators might result in incorrect syntax being passed to the database management system (DBMS)
An invalid query, that results in a DBMS error, might be submitted to the DBMS when you run an SQL procedure query where the following conditions exist:
- A LIBNAME engine is used to access the DBMS.
- The system option DBIDIRECTEXEC is in effect.
- The libref for the DBMS table being created is the same as the libref for the input table.
- The query contains inequality testing where a between could not be used, for example, <= and > or >= and <.
- The query contains a test for a missing value.
The error occurs if you submit a query similar to the following:
options dbidirectexec;
proc sql;
create table dblib.test2 as
select field1
from dblib.test1
where ((field1 >= 101) AND (field1 < 103)) or field1 = .;
quit;
Depending on the DBMS, the error might look similar to the following.
For Oracle
ORACLE_9: Executed: on connection 1
CREATE TABLE bernie2 as select TXT_1."FIELD1" from BERNIE1 TXT_1 where (
TXT_1."FIELD1" is NULL or TXT_1."FIELD1" = 101 thru^ 103 or
(TXT_1."FIELD1" >= 101 and TXT_1."FIELD1" < 103) )
ORACLE: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*
ERROR: ORACLE execute error: ORA-00907: missing right parenthesis.
For DB2
DB2_10: Executed: on connection 1
CREATE TABLE bernie2 as ( select TXT_1."FIELD1" from BERNIE1 TXT_1 where (
TXT_1."FIELD1" is NULL or TXT_1."FIELD1" = 101 thru^ 103 or
(TXT_1."FIELD1" >= 101 and TXT_1."FIELD1" < 103) ) ) WITH NO
DATA
DB2 ERROR:
RESULT OF SQL STATEMENT:
DSN00104E ILLEGAL SYMBOL "THRU". SOME SYMBOLS THAT MIGHT BE LEGAL ARE:
MICROSECONDS MICROSECOND SECONDS SECOND MINUTES MINUTE HOURS
A DUMP OF THE SQLCA FOR THE GET DIAGNOSTICS REQUEST FOLLOWS:
ROW NUMBER: 0 ERROR CONDITION: 1 REASON CODE: 0
SQLCODE: -104 SQLSTATE: 42601 SQLERRP: DSNHPARS For Teradata
TERADATA_26: Executed: on connection 1
CREATE MULTISET TABLE "bernie2" as ( select TXT_1."field1", TXT_1."field2",
TXT_1."field3",
TXT_1."field4" from "bernie1" TXT_1 where ( TXT_1."field4" is NULL or
TXT_1."field4" = 101
thru^ 103 or (TXT_1."field4" >= 101 and TXT_1."field4" < 103) ) ) WITH DATA
TERADATA: trforc: COMMIT WORK
TERADATA: trforc: COMMIT WORK
ERROR: Teradata execute: Syntax error, expected something like an 'OR' keyword
or ')' between an
integer and the word 'thru'.
SQL_IP_TRACE: Some of the SQL was directly passed to the DBMS.
TERADATA: trforc: COMMIT WORK
The error results in the data being pulled into SAS before being passed back to the DBMS to be inserted in the new table. This results in a loss of performance and a potential data integrity issue when SAS cannot store the value as it is stored in the DBMS.
The workaround is to change the test for a null to the following:
WHERE ((field1 >= 101) AND (field1 < 103)) or field1 is null
Operating System and Release Information
| SAS System | Base SAS | z/OS | 9.1 TS1M3 SP4 | 9.4 TS1M1 |
| Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.1 TS1M3 SP4 | |
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.1 TS1M3 SP4 | |
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.1 TS1M3 SP4 | |
| Microsoft Windows XP 64-bit Edition | 9.1 TS1M3 SP4 | |
| Microsoft Windows 2000 Advanced Server | 9.1 TS1M3 SP4 | |
| Microsoft Windows 2000 Datacenter Server | 9.1 TS1M3 SP4 | |
| Microsoft Windows 2000 Server | 9.1 TS1M3 SP4 | |
| Microsoft Windows 2000 Professional | 9.1 TS1M3 SP4 | |
| Microsoft Windows NT Workstation | 9.1 TS1M3 SP4 | |
| Microsoft Windows Server 2003 Datacenter Edition | 9.1 TS1M3 SP4 | |
| Microsoft Windows Server 2003 Enterprise Edition | 9.1 TS1M3 SP4 | |
| Microsoft Windows Server 2003 Standard Edition | 9.1 TS1M3 SP4 | |
| Microsoft Windows XP Professional | 9.1 TS1M3 SP4 | |
| Windows Vista | 9.1 TS1M3 SP4 | |
| Windows Vista for x64 | 9.1 TS1M3 SP4 | |
| 64-bit Enabled AIX | 9.1 TS1M3 SP4 | 9.4 TS1M1 |
| 64-bit Enabled HP-UX | 9.1 TS1M3 SP4 | 9.4 TS1M1 |
| 64-bit Enabled Solaris | 9.1 TS1M3 SP4 | 9.4 TS1M1 |
| HP-UX IPF | 9.1 TS1M3 SP4 | 9.4 TS1M1 |
| Linux | 9.1 TS1M3 SP4 | 9.4 TS1M1 |
| Linux on Itanium | 9.1 TS1M3 SP4 | 9.4 TS1M1 |
| OpenVMS Alpha | 9.1 TS1M3 SP4 | 9.4 TS1M1 |
| Solaris for x64 | 9.1 TS1M3 SP4 | 9.4 TS1M1 |
| Tru64 UNIX | 9.1 TS1M3 SP4 | 9.4 TS1M1 |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2013-10-31 09:11:43 |
| Date Created: | 2013-09-03 15:53:07 |
