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:

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