SUPPORT

Select Your Region

Visit the Cary, NC, US corporate headquarters site

Americas

Europe

Middle East & Africa

Asia Pacific

View our worldwide contacts list for help finding your region

Americas

Europe

Middle East & Africa

Asia Pacific

Sign In

Get access to My SAS, trials, communities and more.

Sign Out

Get access to My SAS, trials, communities and more.

SAS Sites

sas.com
Support
Blogs
Communities
Developer
Curiosity
Videos

Documentation

Learn
Brand
PartnerNet
Merchandise
SUPPORT / SAMPLES & SAS NOTES
 

Support

Problem Note 39271: Outer join conditions might not appear in a WHERE clause when performing query against Oracle

DetailsHotfixAboutRate It

When you use a LIBNAME statement to perform an outer-join query that involves multiple SQL views against Oracle tables, it is possible that one of the outer-join criteria might be left out of the WHERE clause. For example, suppose you have a query that involves an outer join of an Oracle table and three SQL views that access other Oracle tables. When this outer join is converted from the SAS® syntax to a former Oracle approach that uses a plus sign (+) for outer joins in the WHERE clause, only two of the three join conditions are generated in the WHERE clause. An incomplete query such as this creates a Cartesian product with incorrect results.

For example, the following query joins one Oracle table that is accessed directly and three SQL views that all reference another Oracle table:

select from mdm.household summary left join work.loan_view on (household_summary.household_id=loan_view.household_id) left join work.demo_view on (household_summary.household_id=demo_view.household_id) left join work.geo_view on (household_summary.household_id=geo_view.household_id);

The WHERE clause that is generated for this query should contain three conditions, as follows:

where household_summary."household_id"=geo_view.";household_id" (+) and household_summary."household_id"=demo_view."household_id" (+) and household_summary."household_id"=loan_view."household_id" (+)

Instead, the WHERE clause contains only two conditions:

where household_summary."household_id"=geo_view."household_id" (+) and household_summary."household_id"=demo_view."household_id" (+)

Currently, there is no solution or workaround for this problem.



Operating System and Release Information

Product FamilyProductSystemSAS Release
ReportedFixed*
SAS SystemBase SASMicrosoft Windows 2000 Advanced Server9.1 TS1M3 SP4
Microsoft Windows 2000 Datacenter Server9.1 TS1M3 SP4
Microsoft Windows XP 64-bit Edition9.1 TS1M3 SP49.2 TS1M0
Microsoft Windows Server 2003 Datacenter 64-bit Edition9.1 TS1M3 SP49.2 TS1M0
Microsoft Windows Server 2003 Enterprise 64-bit Edition9.1 TS1M3 SP49.2 TS1M0
z/OS9.1 TS1M3 SP49.2 TS1M0
Microsoft® Windows® for 64-Bit Itanium-based Systems9.1 TS1M3 SP49.2 TS1M0
Microsoft Windows 2000 Server9.1 TS1M3 SP4
Microsoft Windows 2000 Professional9.1 TS1M3 SP4
Microsoft Windows NT Workstation9.1 TS1M3 SP4
Microsoft Windows Server 2003 Datacenter Edition9.1 TS1M3 SP49.2 TS1M0
Microsoft Windows Server 2003 Enterprise Edition9.1 TS1M3 SP49.2 TS1M0
Microsoft Windows Server 2003 Standard Edition9.1 TS1M3 SP49.2 TS1M0
Microsoft Windows XP Professional9.1 TS1M3 SP49.2 TS1M0
Windows Vista9.1 TS1M3 SP49.2 TS1M0
64-bit Enabled AIX9.1 TS1M3 SP49.2 TS1M0
64-bit Enabled HP-UX9.1 TS1M3 SP49.2 TS1M0
64-bit Enabled Solaris9.1 TS1M3 SP49.2 TS1M0
HP-UX IPF9.1 TS1M3 SP49.2 TS1M0
Linux9.1 TS1M3 SP49.2 TS1M0
Linux on Itanium9.1 TS1M3 SP49.2 TS1M0
OpenVMS Alpha9.1 TS1M3 SP49.2 TS1M0
Solaris for x649.1 TS1M3 SP49.2 TS1M0
Tru64 UNIX9.1 TS1M3 SP49.2 TS1M0
* 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.