Skip to Content
Technical Articles
Author's profile photo rathish s

How to resolve Outer Join generation Issue in the Business Object Migration project (Netezza to PostgreSQL)



After migrating the universe from Netezza to PostgreSQL, the outer joins are generated the opposite way in YB compared to Netezza causing record drop.


Netezza generated query brings 5 records but PostgreSQL generated query brings 1 record.


The value ANSI 92 generates an outer join in the FROM clause. Other values generate the outer join in the WHERE clause.

When you modify this setting, you should check join properties to verify that the outer join expression is valid, and that the cardinalities are correct. ANSI92 does not support any manual customization in the join syntax.


The PRM file OUTERJOINS_GENERATION parameter relates to the universe ANSI92 setting in the following way:

If the PRM file OUTERJOINS_GENERATION parameter is set to ANSI92 and the universe ANSI92 setting is set to NO, the PRM parameter overrides the universe setting and outer joins conform to ANSI92 behavior.

If the PRM file OUTERJOINS_GENERATION parameter is set to USUAL, then the universe ANSI92 setting takes precedence, and outer joins conform to ANSI92 depending on whether the universe ANSI92 setting is YES or NO.


The ANSI92 value makes REVERSE_TABLE_WEIGHT parameter not useful for optimization of SQL generation. Outer joins that conform to ANSI92 behavior leads the order of the tables in the SQL sentence.


Configuration steps(prm file)

Step 1: Login to BO server

Step 2: Go to C:\Program Files (x86)\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer\odbc

Step 3: Take the backup of the particular database driver .prm file

Step 4: Add the below parameter in the .prm file and save the file

<Parameter Name=”OUTERJOINS_GENERATION”>ANSI92</Parameter>

Step 5: Restart the BO server

Step 6: Run/Refresh/schedule the report.


Solution 2

Configuration steps (Universe Level)

Step 1: Import the universe from Repository

Step 2: Go to the parameters and click the parameter tab

Step 3: Change the ANSI92 value from No to Yes

Step 4: Save the changes and export the universe to repository




SAP Portal Reference

Feedback and comments are appreciated Thanks for stopping by, and hope this helps at least a few developers/users out there.,

Assigned Tags

      1 Comment
      You must be Logged on to comment or reply to a post.
      Author's profile photo Riadh Saaidia
      Riadh Saaidia



      Thank you for this post.

      • Solution 1 : impacts all the universes. Solution 2: Only selected universe will be changed. isn't it?
      • Changing  these parameters would it change other joins in the UDT univers ?

      I have the same issue but after publishing and exporting one 4.2 univers, when the customer imported the univers via Biar file in a 4.3 Sp2 environment, in UDT he has some joins with inversed check boxes between some tables, no manual modification made.

      Thank you for your reply.

      Any idea for a solution would be welcome.


      Best Regards