Usage Note 20123: Incorrect SQL text is generated when inner and left joins are used
SAS/ACCESS® Interface to DB2 generates incorrect SQL text when you run
an SQL query that contains an inner and left join. DB2 returns an error
that causes SAS to process the join, resulting in poor performance.
The error message that DB2 displays is as follows:
ERROR: CLI describe error: [IBM][CLI Driver][DB2/NT] SQL0338N An ON
clause associated with a JOIN operator is not valid. SQLSTATE=42972
This problem occurs if the tables in the ON clause appear in the
opposite order from what they are listed in the inner join. This query
fails using explicit pass-through, as well.
The solution is to rewrite the statement and specify the tables in the
proper order.
The following query does NOT list the tables in the proper order:
SELECT d.advisor, p.empnum, e.teacher
FROM DB2PRD.students AS d
INNER JOIN DB2PRD.staff AS p ON (p.empnum = d.advisor)
left JOIN DB2PRD.classes AS e ON (e.teacher = d.advisor);
You need to rewrite this statement, as follows:
SELECT d.advisor, p.empnum, e.teacher
FROM DB2PRD.staff AS p
INNER JOIN DB2PRD.students AS d ON (d.advisor = p.empnum)
left JOIN DB2PRD.classes AS e ON (e.teacher = d.advisor);
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 | | | 8.2 TS2M0 | |
| Microsoft Windows 95/98 | | | 8 TS M1 | |
| Microsoft Windows NT Workstation | | | 8 TS M1 | |
| Windows Vista | 9.2 | | | |
| Microsoft Windows XP 64-bit Edition | 9.2 | | | |
| Microsoft® Windows® for 64-Bit Itanium-based Systems | | | 9.1 TS1M0 | |
| Microsoft Windows Server 2003 Standard Edition | 9.2 | | | |
| Microsoft Windows Server 2003 Enterprise Edition | 9.2 | | | |
| Microsoft Windows Server 2003 Datacenter Edition | | | 9.1 TS1M0 | |
| Microsoft Windows 2000 Server | | | 9.1 TS1M0 | |
| Microsoft Windows 2000 Professional | | | 9.1 TS1M0 | |
| Microsoft Windows 2000 Datacenter Server | 9.2 | | | |
| Microsoft Windows 2000 Advanced Server | | | 9.1 TS1M0 | |
| Solaris | | | 8 TS M1 | |
| z/OS | | | 8 TS M1 | |
| IRIX | | | 8 TS M1 | |
| 64-bit Enabled Solaris | 9.2 | | | |
| Solaris for x64 | 9.2 | | | |
| Linux | | | 8.2 TS2M0 | |
| ABI+ for Intel Architecture | | | 8 TS M1 | |
| HP-UX | | | 8 TS M1 | |
| HP-UX IPF | | | 9.1 TS1M0 | |
| 64-bit Enabled HP-UX | | | 8 TS M1 | |
| 64-bit Enabled AIX | 9.2 | | | |
| Tru64 UNIX | 9.2 | | | |
| AIX | | | 8 TS M1 | |
*
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: | Usage Note |
| Priority: | high |
| Topic: | SAS Reference ==> SQL
SAS Reference ==> Procedures ==> SQL
|
| Date Modified: | 2007-05-24 09:43:43 |
| Date Created: | 2007-05-02 10:59:32 |
