Use of Outer Joins in Combination with Self Restricting Joins

Problem

BusinessObjects use some algorithm for SQL generation based on objects selected in the query and the universe. There are a number of parameters that control SQL generation. The problem is that in some cases BO generates SQL that is not intuitively expected.

What we would expect is that the self restricting joins are applied to the tables and then the tables are joined.

When SELFJOINS_IN_WHERE=No, the self restricting joins are put in the ON clause of the SQL. The generated SQL ignores the self restricting join on the table FACT.

SELECT DISTINCT
  FACT.A,
  DIM.B
FROM
  DIM  RIGHT OUTER JOIN  FACT
  ON  (FACT.DIM_ID=DIM.DIM_ID  AND  FACT.TYPE=0  AND  DIM.TYPE=0)

When SELFJOINS_IN_WHERE=Yes, the self restricting joins are put in the WHERE clause.  The effect of this is that the outer join turns into inner join in the generated SQL.

SELECT DISTINCT
  FACT.A,
  DIM.B
FROM
  DIM  RIGHT OUTER JOIN  FACT
  ON  (FACT.DIM_ID=DIM.DIM_ID)
WHERE
  ( FACT.TYPE=0  )
  AND  ( DIM.TYPE=0  )

In fact, we expect the following SQL:

SELECT DISTINCT
  FACT.A,
  DIM.B
FROM
  DIM  RIGHT OUTER JOIN  FACT
  ON  (FACT.DIM_ID=DIM.DIM_ID  AND  DIM.TYPE=0)
WHERE
  ( FACT.TYPE=0 )

BusinessObjects cannot place self restricting joins conditionally depending on the join structure. (Probably this will make the algorithm too complex, so this is not a bad thing)

A workaround is to avoid using of self joins using derived tables.

A Solution when SELFJOINS_IN_WHERE=No

Replace FACT with a derived table FACT_D

SELECT DIM_ID, A FROM FACT WHERE FACT.TYPE=0

The generated SQL will be:

SELECT DISTINCT
  FACT_D.A,
  DIM.B
FROM
  DIM RIGHT OUTER JOIN (
  SELECT DIM_ID, A FROM FACT WHERE FACT.TYPE=0
  ) FACT_D
  ON (DIM.TYPE=0  AND  FACT_D.DIM_ID=DIM.DIM_ID)

A Solution when SELFJOINS_IN_WHERE=Yes

Replace DIM with a derived table DIM_D

SELECT DIM_ID, B FROM DIM WHERE DIM.TYPE=0

The generated SQL will be:

SELECT DISTINCT
  FACT.A,
  DIM_D.B
FROM
  (
  SELECT DIM_ID, B FROM DIM WHERE DIM.TYPE=0
  ) DIM_D RIGHT OUTER JOIN FACT
  ON (FACT.DIM_ID=DIM_D.DIM_ID)
WHERE
  ( FACT.TYPE=0 )