Problem Note 4451: Query submitted to DBMS may generate incorrect results
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
| SAS System | Base SAS | Microsoft Windows 95/98 | 8 TS M0 | |
| Microsoft Windows 2000 Server | 8 TS M0 | 9 TS M0 |
| Microsoft Windows NT Workstation | 8 TS M0 | 9 TS M0 |
| Microsoft Windows 2000 Professional | 8 TS M0 | 9 TS M0 |
| Microsoft Windows 2000 Datacenter Server | 8 TS M0 | 9 TS M0 |
| Microsoft Windows 2000 Advanced Server | 8 TS M0 | 9 TS M0 |
| OpenVMS VAX | 8 TS M0 | |
| Solaris | 8 TS M0 | |
| 64-bit Enabled Solaris | 8 TS M0 | |
| IRIX | 8 TS M0 | |
| OS/2 | 8 TS M0 | |
| z/OS | 8 TS M0 | 9 TS M0 |
| Linux | 8.2 TS2M0 | 9 TS M0 |
| HP-UX | 8 TS M0 | |
| 64-bit Enabled HP-UX | 8 TS M0 | |
| ABI+ for Intel Architecture | 8 TS M0 | |
| OpenVMS Alpha | 8 TS M0 | 9 TS M0 |
| CMS | 8 TS M0 | |
| Tru64 UNIX | 8 TS M0 | 9 TS M0 |
| 64-bit Enabled AIX | 8 TS M0 | |
| AIX | 8 TS M0 | |
*
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: | alert |
| Topic: | SAS Reference ==> Procedures ==> SQL SAS Reference ==> SQL
|
| Date Modified: | 2004-11-22 12:47:31 |
| Date Created: | 2001-03-07 09:39:17 |