# 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 )```

## 6 thoughts on “Use of Outer Joins in Combination with Self Restricting Joins”

1. Elisabeth

Wow, your blog has become somewhat of a BO bible to me lately! Whatever challenge I am facing, you always have a very educational way of clearing things up for me. I am very impressed!

As for this outer join/self join issue, we have so far been hacking it a bit. We have not been using ANSI-92, and in Oracle we have hard coded the join to include and “outer” the self join, like this:
DIM.DIM_ID(+) = FACT.DIM_ID AND DIM.TYPE(+) = 0
by selecting the two columns in the join editor, ticking the outer join box, and typing in the last bit “AND DIM.TYPE(+) = 0”.

The problem now is that we want to go ANSI92 to be able to use the same universe for clients on Oracle and SQL Server, and thus no more hard coding joins. So I guess the way to go will be derived tables for us, right? Because it’s not possible to “outer” a self join any other way?

And how do I decide if I should have YES or NO for SELFJOINS_IN_WHERE?

Thanks again for your excellent blog. Now I’m off to read “How to Replace a Table with a Derived Table” π

Like

1. dmytro Post author

Should YES or NO be used for SELFJOINS_IN_WHERE?

The default is NO and this seems to be the most logical behavior.
YES might be reasonable when all joins are INNER.

Like

2. dmytro Post author

Regarding DIM.DIM_ID(+) = FACT.DIM_ID AND DIM.TYPE(+) = 0

Yes, in this situation the correct way is to “embed” the self join into derived table

Like

2. ganesh

Great explanation.
Seems there is an error in the SQL of FACT_D.
It should be like SELECT DIM_ID, A FROM FACT WHERE FACT.TYPE=0
instead of SELECT DIM_ID, B FROM DIM WHERE DIM.TYPE=0.

Correct me if am i wrong. π

Like