Providing software solutions since 1976

• Log in
• Create profile
• Welcome
  [Log out]
• Edit profile

• Print  |
• Feedback  |
• Share

  • E-mail

  
  
  

  E-mail This Page

  Your Name:*
  Your E-mail:*
  Recipient E-mail:*
  Send a copy to me
  Personal Message:
  
  

  * indicates a required field

When querying a DBMS table using a LIBNAME engine from within PROC
SQL, it is possible to get incorrect results.  This problem can occur
if all the following conditions are true.  If your original query:

  1) contains a calculated field on the SELECT,
  2) contains an ORDER BY clause, and
  3) the WHERE clause contains

    i) two BETWEEN operators based on the same variable, or
   ii) a BETWEEN operator and a NOT EQUAL condition based
       on the same variable.

For example:

  select a,b||c as new, d
  from dbmslib.table
  where d between 90 and 100 or d between 1 and 10
  order by a;

or:

  select a,b||c as new, d
  from dbmslib.table
  where d between 1 and 5 and d ne 3
  order by a;

In the first example, the WHERE submitted to the DBMS looks like WHERE
D BETWEEN 1 AND 10.  The lowest BETWEEN condition is submitted, while
the other condition is left off the query.  This results in an
incorrect partial retrieval of the desired data.

In the second example, the WHERE submitted to the DBMS looks like
WHERE D>=1 and D<3.  Once again, part of the query is left off (i.e.
D=4 AND D=5).

To circumvent the problem, use the pass-through facility instead of
the LIBNAME engine.


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

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

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#004451

Operating System and Release Information

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: