OBIEE - Full outer Join


To perform a SQL - Full Outer Join, you have two ways :

To know what sort of join is a full outer join, follow this link : SQL - Full Outer Join.

The full outer join is often a sign that you are in presence of a preservation of the dimensions: OBIEE - Densification / Dimensions Preservation Possibilities

In the repository

With a vertical fragmentation

To have example on how to design a fact vertical partitioning capabilities to perform a full outer join : OBIEE - Fact-based vertical partitioning/fragmentation

In the logical join

In the logical join of the business model layer:

With the Obiee Logical Sql (In Answer for instance)

To perform a FULL OUTER JOIN, you can simply manage it with a logical sql that you can set for instance in the advanced tab of an answer. And for a step by step guide, it's here: OBIEE 10G - Reporting on Multiple Subject Area (Advanced Logical SQL)

Example of full outer join with the Obiee logical sql :

 SELECT A.saw_0, B.saw_0  FROM 
(SELECT Calendar."Calendar Year" saw_0 FROM SH WHERE Calendar."Calendar Year" IN (2000, 2001) )  A FULL OUTER JOIN
(SELECT Calendar."Calendar Year" saw_0 FROM SH WHERE Calendar."Calendar Year" IN (1999, 2000, 2001) )  B ON A.saw_0 = B.saw_0 


Issues with Full Outer Join and WITH Clause on Oracle Database 10g

If you use a version of Oracle Database 10g Release 2 prior to, then you might encounter an issue with a Full Outer Join that causes severe database performance issues and appears to hang the database when the Oracle BI Server sends to the database SQL queries that include WITH clauses.

To work around this issue when using Oracle Database 10g Release 1 or 2, upgrade to Oracle Database 10g Release 2 Patch Set 2 (for For performance reasons, this is the preferred workaround.

To work around this issue without installing Patch Set 2, modify the Database Features table using the Administration Tool. Set to true :


This configuration prevents the Oracle BI Server from sending SQL constructs that expose the issue in an unpatched Oracle Database 10g and you can see the behaviour of this features in this article: OBIEE - How to control the use of the WITH CLAUSE and of STITCH Join ?

Documentation / Reference

Powered by ComboStrap