SUPPORT / SAMPLES & SAS NOTES
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
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| 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 | |||