Problem Note 20116: SAS/ACCESS® software fails to use table aliases correctly when you use in-line SELECT statements
SAS/ACCESS software fails to use table aliases correctly when you use
in-line SELECT statements. As a result, you might receive error messages
similar to the following:
ERROR: ORACLE prepare error: ORA-00904: "CLASS"."SEX": invalid
identifier. SQL statement: select t1."NAME", t2."AGE"
from CLASS t1, CLASS t2 where (t1."AGE" = t2."AGE") and
(CLASS."SEX" = 'F') and (CLASS."SEX" = 'M').
ERROR: CLI describe error: [IBM][CLI Driver][DB2/LINUX] SQL0206N
"CLASS.SEX" is not valid in the context where it is used.
SQLSTATE=42703
Consider the following simplified example. Note that the WHERE clauses
(that were originally in the two SELECT statements) still use the table
alias CLASS rather than the T1 and T2 defined aliases. This is not valid
syntax in DB2, Oracle, or other databases.
libname db2 db2 db=sample;
proc sql _tree;
create table x as
select t1.name, t2.age from
(select age, name from db2.class where sex='F') as t1,
(select age from db2.class where sex='M') as t2
where t1.age=t2.age;
quit;
/* The DBMS SELECT statement that is generated by SAS.
select t1."NAME", t2."AGE"
from CLASS t1,
CLASS t2
where (t1."AGE" = t2."AGE")
and (CLASS."SEX" = 'F')
and (CLASS."SEX" = 'M')
*/
This issue results in in-line views that are not persisted. The in-line
views should persist for the proper scoping of the predicates. The
predicate qualifiers would be correct if they were contained within the
appropriate in-line view. As a result, this problem query is not passed
down correctly through Implicit Pass-Through at any prior release.
If your query is critical to performance, you can work around the
problem (in the short term) by using Explicit Pass-Through or predefined
views in the database.
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® for 64-Bit Itanium-based Systems | 9.1 TS1M3 | 9.2 TS2M0 |
| Microsoft Windows XP 64-bit Edition | 9.1 TS1M3 | 9.2 TS2M0 |
| Microsoft Windows NT Workstation | 9.1 TS1M3 | |
| Microsoft Windows Server 2003 Standard Edition | 9.1 TS1M3 | 9.2 TS2M0 |
| Microsoft Windows Server 2003 Enterprise Edition | 9.1 TS1M3 | 9.2 TS2M0 |
| Microsoft Windows 2000 Server | 9.1 TS1M3 | 9.2 TS2M0 |
| Microsoft Windows Server 2003 Datacenter Edition | 9.1 TS1M3 | 9.2 TS2M0 |
| Microsoft Windows 2000 Datacenter Server | 9.1 TS1M3 | 9.2 TS2M0 |
| Microsoft Windows 2000 Professional | 9.1 TS1M3 | 9.2 TS2M0 |
| Microsoft Windows 2000 Advanced Server | 9.1 TS1M3 | 9.2 TS2M0 |
| 64-bit Enabled Solaris | 9.1 TS1M3 | 9.2 TS2M0 |
| Linux | 9.1 TS1M3 | 9.2 TS2M0 |
| 64-bit Enabled HP-UX | 9.1 TS1M3 | 9.2 TS2M0 |
| Microsoft Windows XP Professional | 9.1 TS1M3 | 9.2 TS2M0 |
| 64-bit Enabled AIX | 9.1 TS1M3 | 9.2 TS2M0 |
*
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: | high |
| Topic: | SAS Reference ==> SQL
SAS Reference ==> Procedures ==> SQL
|
| Date Modified: | 2008-02-18 14:21:04 |
| Date Created: | 2007-05-02 09:31:53 |
