20419 - Joining SQL Views of Oracle tables that contain where clauses results in invalid SQL query

Problem Note 20419: Joining SQL Views of Oracle tables that contain where clauses results in invalid SQL query

Joining 2 SQL views of DBMS tables that contain where clauses yields incorrect SQL being passed to the DBMS.

Using Oracle will return the following error:

   ERROR: ORACLE prepare error: ORA-00904: "CELL_DW"."DW_DIM_BAN"."BAN1":
   invalid identifier.

This error is introduced at 9.1.3 SP4 and does not occur in SP3.

Operating System and Release Information

Product Family	Product	System	SAS Release
Product Family	Product	System	Reported	Fixed*
SAS System	SAS/ACCESS Interface to CA-Datacom/DB	Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
SAS System	SAS/ACCESS Interface to CA-IDMS	Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
SAS System	SAS/ACCESS Interface to CA-OpenIngres	Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
SAS System	SAS/ACCESS Interface to DB2	Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
SAS System	SAS/ACCESS Interface to DB2 Server for VM	Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
SAS System	SAS/ACCESS Interface to IMS-DL/I	Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
SAS System	SAS/ACCESS Interface to Informix	Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
SAS System	SAS/ACCESS Interface to MySQL	Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
SAS System	SAS/ACCESS Interface to ODBC	Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
SAS System	SAS/ACCESS Interface to OLE DB	Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
SAS System	SAS/ACCESS Interface to Oracle	Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
SAS System	SAS/ACCESS Interface to Oracle Rdb	Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
SAS System	SAS/ACCESS Interface to PC Files	Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
SAS System	SAS/ACCESS Interface to PeopleSoft	Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
SAS System	SAS/ACCESS Interface to R/3	Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
SAS System	SAS/ACCESS Interface to SAP BW	Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
SAS System	SAS/ACCESS Interface to Sybase	Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
SAS System	SAS/ACCESS Interface to Teradata	Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
SAS System	SAS/ACCESS Interface to Adabas	Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4
SAS System	SAS/ACCESS Interface to Baan	Microsoft Windows 2000 Professional	9.1 TS1M3 SP4
		Microsoft Windows XP Professional	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Datacenter Server	9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3 SP4

* 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 ==> LIBNAME Engines

Date Modified:	2008-03-07 14:38:54
Date Created:	2007-06-20 10:32:39

Support

Problem Note 20419: Joining SQL Views of Oracle tables that contain where clauses results in invalid SQL query

Operating System and Release Information