Problem Note 16547: Correlated subqueries using column aliases may not be passed to your
dbms when using SAS/ACCESS LIBNAME statement
PROC SQL queries containing correlated subqueries may not be passed to
the dbms with implicit passthru. An example of such a query is:
proc sql;
select prodname as products, count(distinct custname) as cound
from dbmslib.invoice as i1
group by prodname
having (select sum(invqty) from invoice
where prodname=i1.prodname) > 30
order by 1,2;
quit;
You SAS code may also generate dbms-specific error messages due to
column aliases incorrectly appearing in conditional expressions. For
example, with the following query:
proc sql;
select a.name as nalias, a.sex
from kw.t1 a, kw.t1 b
where a.sex='F' and a.name in
(select name from kw.t1 c where a.name=c.name) and a.sex='F';
PROC SQL passes the following query to the dbms:
select a."NAME" as nalias, a."SEX"
from T1 a, T1 b
where a."NAME" in (select c."NAME" from T1 c
where nalias = c."NAME" ) and (a."SEX" = 'F')
You will see the following error when this query is run against an
Oracle database. Various other databases will generate similar errors.
ERROR: ORACLE prepare error: ORA-00904: "NALIAS": invalid identifier.
SQL statement: select a."NAME" as nalias, a."SEX" from T1 a, T1 b
where a"NAME" in (select c."NAME" from T1 c where nalias = c."NAME")
and (a."SEX" = 'F')
The alias 'nalias' appearing in the subquery is incorrect. There
currently is no circumvention for this problem.
Select the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| SAS System | Base SAS | Microsoft Windows XP Professional | 9.1 TS1M3 | |
| Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.1 TS1M3 | |
| Microsoft Windows XP 64-bit Edition | 9.1 TS1M3 | |
| Microsoft Windows NT Workstation | 9.1 TS1M3 | |
| Microsoft Windows Server 2003 Standard Edition | 9.1 TS1M3 | |
| Microsoft Windows Server 2003 Datacenter Edition | 9.1 TS1M3 | |
| Microsoft Windows Server 2003 Enterprise Edition | 9.1 TS1M3 | |
| Microsoft Windows 2000 Datacenter Server | 9.1 TS1M3 | |
| Microsoft Windows 2000 Professional | 9.1 TS1M3 | |
| Microsoft Windows 2000 Server | 9.1 TS1M3 | |
| z/OS | 9.1 TS1M3 | |
| 64-bit Enabled Solaris | 9.1 TS1M3 | |
| Microsoft Windows 2000 Advanced Server | 9.1 TS1M3 | |
| Linux | 9.1 TS1M3 | |
| Linux on Itanium | 9.1 TS1M3 | |
| HP-UX IPF | 9.1 TS1M3 | |
| 64-bit Enabled HP-UX | 9.1 TS1M3 | |
| OpenVMS Alpha | 9.1 TS1M3 | |
| 64-bit Enabled AIX | 9.1 TS1M3 | |
| Tru64 UNIX | 9.1 TS1M3 | |
*
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: | medium |
| Topic: | SAS Reference ==> Procedures ==> SQL
SAS Reference ==> SQL
|
| Date Modified: | 2007-04-17 17:29:29 |
| Date Created: | 2005-11-17 11:42:06 |
