PK89111: INFORMIX: DATA INCONSISTANCY BECAUSE OUTER JOIN EXECUTED AS INNER JOIN

APAR status

• Closed as program error.

Error description

• LEFT OUTER join defined in Framework Manager is not included in
  the Native SQL nor a splitted SELECT is generated to execute
  thejoin locally.
    This results in wrong data results!
  
  Checking the Relationship SQL in Framework Manager does not
  showproblem. Here the LEFT OUTER join is visible, but the result
     does not reflect it!
    Selecting one item from each table visualize the major
   difference:
  The Cognos SQL includes the LEFT OUTER join.
  The Native SQL defines an INNER join within the WHERE clause
  because <table1.itemA> = <table2.itemB>!
  
  ReportStudio (and other studios will behave the same way but
  notparticularyl tested) generates the same wrong SQL than
  FrameworkManager and therefore returns wrong data!
  
         This wrong SQL generation happens for Informix IDS 9.40
  and 10. For Informix 11.10 also the native SQL includes the LEFT
  OUTER  join.
  
  
      We have to modify the cogdmif.ini, parameter Joined=F and
  set
  it to ?T? which is n o t the default in 8.4 for Informix IDS
  9.40. Then the LEFT OUTER JOIN syntax is generated for Native
  SQL. (see IR #615552 too)
  
  
       Related problems:
      Problem: 586310.0
     Title: Informix and both Inner and Outer Join queries results
  insplit selects
   -> fixed in 8.4
  ->Workaround Description [Exceptions Joins] Inner=T
  ==>> this fix might cause the wrong SQL generation.
  

Local fix

• Modify the cogdmif.ini for IDS 9.40:
  In section [Exceptions Tables Database:IDS INFORMIX9.40]
  Derived=F
  Joined=F -> change to Joined=T
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Users                                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See error description.                                       *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Cognos BI Server 10.2                             *
  ****************************************************************
  

Problem conclusion

• Code Fix
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  COG8 BI REPORT

• Fixed component ID

  5724W12RS

Applicable component levels

• RA20 PSN

     UP