• Print |
• E-mail  |
• Bookmark

You may receive different results submitting the same query using
PROC SQL pass-through versus the LIBNAME Access engine.

There is a potential data integrity exposure when passing SAS SQL
queries to a DBMS when the query contains an EXISTS predicate. The
problem is that any WHERE clause contained within a subquery associated
with the EXISTS will not be generated. For example:

SELECT  DISTINCT IDX
  FROM    libdb.mytab aNot1
  WHERE   (userx IN ("bob"))
    AND   (not EXISTS (SELECT DISTINCT IDX
                       FROM   libdb.mytab bNot1
                            WHERE  aNot1.IDX = bNot1.IDX
                            ^^^^^ This above line does not get
                                  generated.
           AND  TYPEX IN ("car" )));

The WHERE clause outside of the EXISTS subquery works correctly.
The problem is only with a WHERE clause contained within an EXISTS
subquery.

Although a user can circumvent this problem by turning off passing down
queries using DIRECT_SQL=NO on the LIBNAME statement, there is a
specific coding workaround that will serve as a viable
replacement. One could code the above as:

SELECT  DISTINCT IDX
    FROM    libdb.mytab aNot1
    WHERE   (userx IN ("bob"))
        AND   IDX NOT IN (SELECT DISTINCT IDX
                            FROM   libdb.mytab bNot1
                              WHERE  aNot1.IDX = bNot1.IDX
                                AND  TYPEX IN ("car") );

The IN predicate is similar to EXISTS (not identical, but can often be
used in place of it). The WHERE clause contained within the subquery
associated with the IN predicate will get generated correctly, so a user
should consider coding similar queries using IN rather than EXISTS.


A Technical Support hot fix for Release 8.1 TSLEVEL TS1M0 for this
issue is available at:

http://www.sas.com/techsup/download/hotfix/81_sbcs_prod_list.html#003976


A Technical Support hot fix for Release 8.2 TSLEVEL TS2M0 for this
issue is available at:

http://www.sas.com/techsup/download/hotfix/82_sbcs_prod_list.html#003976

For customers running SAS with Asian Language Support (DBCS), this
hot fix should be downloaded from:

http://www.sas.com/techsup/download/hotfix/82_dbcs_prod_list.html#003976


This will also be resolved at SAS V9.

Operating System and Release Information

A fix for Release 8.1 (TS1M0) for this issue is available at:

A fix for Release 8.2 (TS2M0) for this issue is available at:

A fix for Release 8.2 (TS2M0) with Asian Language Support (DBCS) for this issue is available at: