Problem

(It is assumed that the parameter ANSI92 of the universe is set to Yes, the parameter SELFJOINS_IN_WHERE is either not set or is set to No.)

The table A is affected by the self/outer join issue if it has a self join and it is joined to a table B with the following outer join:

If a query use these two tables, the self join SQL will be put into the outer join SQL causing the generated SQL query to ignore the self join.

If the query contains at least one inner join of A with another table, the issue will not appear because the self join will be put into the inner join SQL, so the self join will be applied to the table A. Due to this, the issue appear rarely.

Example 1

The table structure contains two tables BOEMPLOYEE and EXVENDOR. BOEMPLOYEE has a field ACCESSUSERNAME which is used to implement access control to the table.

If the query uses these two tables, the self join will be put into the outer join SQL and therefore ignored:

SELECT
  BOEMPLOYEE.NAME,
  EXVENDOR.NAME
FROM EXVENDOR RIGHT OUTER JOIN BOEMPLOYEE
  ON (BOEMPLOYEE.VENDORNUMBER=EXVENDOR.VENDORNUMBER
      AND BOEMPLOYEE.ACCESSUSERNAME = @VARIABLE('BOUSER'))

Example 2

Here we have a table EXFINANCEENTRY that is joined with BOEMPLOYEE using inner join.

In this case, the self join the self join will be put both into the outer join SQL and inner join SQL. Because it is put in the inner join, the self join is applied to the BOEMPLOYEE and the issue will not appear:

SELECT
  EXFINANCEENTRY.TRANSACTIONNUMBER,
  BOEMPLOYEE.NAME,
  EXVENDOR.NAME
FROM
  EXVENDOR RIGHT OUTER JOIN BOEMPLOYEE
  ON (BOEMPLOYEE.VENDORNUMBER=EXVENDOR.VENDORNUMBER
      AND BOEMPLOYEE.ACCESSUSERNAME = @VARIABLE('BOUSER'))
  INNER JOIN EXFINANCEENTRY
  ON (EXFINANCEENTRY.EMPLOYEENUMBER=BOEMPLOYEE.EMPLOYEENUMBER
      AND BOEMPLOYEE.ACCESSUSERNAME = @VARIABLE('BOUSER'))

The issue in BO XI 3.1 FP3.7, FP3.8, FP4.2, FP5.2

In the mentioned FPs, the algorithm of generation of joins has changed: the self join is put only in one join SQL connected to the table. (This is error that tracked under the problem report id ADAPT01640966. SAP KB article 1743593 – Self join is ignored by SQL query generator in WebIntelligence Rich Client and Universe Designer in SAP BusinessObjects XI 3.1)

Example 3

The self join is ignored even though there is an inner join:

SELECT
  EXFINANCEENTRY.TRANSACTIONNUMBER,
  BOEMPLOYEE.NAME,
  EXVENDOR.NAME
FROM
  EXVENDOR RIGHT OUTER JOIN BOEMPLOYEE
  ON (BOEMPLOYEE.VENDORNUMBER=EXVENDOR.VENDORNUMBER
      AND BOEMPLOYEE.ACCESSUSERNAME = @VARIABLE('BOUSER'))
  INNER JOIN EXFINANCEENTRY
  ON (EXFINANCEENTRY.EMPLOYEENUMBER=BOEMPLOYEE.EMPLOYEENUMBER)

Solution

Either you using the mentioned FPs or not, the described situation is a design flaw of the universes. In such situations, the self join should be embedded into the derived table.

For the above examples, the derived table can be following

SELECT * FROM  BOEMPLOYEE WHERE ACCESSUSERNAME = @VARIABLE('BOUSER'))

The table BOEMPLOYEE should be replaced with this derived table.

See also

Use of Outer Joins in Combination with Self Restricting Joins
How to Replace a Table with a Derived Table