After you apply hot fixes E9BB80, E9BB98 and E9BB99 or you run SAS 9.2, submitting a query that includes an in-line view, an outer join, and a WHERE clause can result in the WHERE conditions being added to the ON clause. Consequently, incorrect results might be generated.

For example, the results of the following query illustrates the incorrect behavior:

proc sql;
   create table fragerror as
   select a.* from
      (select distinct * from db2lib.where_on_issue
       where fragtype ^= 'B') a left join
      (select distinct * from db2lib.where_on_issue
                         where fragtype='B') b 
                         on a.cd=b.cd where 
                                      (a.startdate < b.startdate or
                                       a.enddate > b.enddate);
   quit;

In this example, when the ON A.CD=B.CD WHERE . . . clause is passed to the database, the WHERE is replaced with the AND operator, as shown here:

on a."cd" = b."cd" 
and (a."startdate" < b."startdate") or (a."enddate" > b."enddate") 

The WHERE-clause conditions are now part of the ON clause, which can generate undesired results.

There is currently no workaround for this problem.

Select the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

A fix for SAS 9.1.3 (9.1 TS1M3) for this issue is available at:

A fix for SAS 9.1.3 (9.1 TS1M3) with Asian Language Support (DBCS) for this issue is available at: