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 )
bukhantsov.org 