1576244 - Conditional placement of self-restricting joins.

• Self-restricting joins are placed in the FROM or in the WHERE clauses depending on the value of SELFJOINS_IN_WHERE parameter.
• Need more control over placement of self-restriction joins in generated SQL.
• BusinessObjects should determine the correct way of self-restiction placement according to the join type when generating SQL.

• SAP BusinessObjects XI 3.1
• Universe Designer

Example on the simplified "club" universe:

1. Create universe based on two tables joined in the following manner (see the screenshot).

2. Create self-restricting joins on each table. For Service table it is Service.service='Accomodation' and for Service_line it is Service_Line.resort_id='1.0'.

3. Create the report based on this universe and select service and service_line objects.  

4. When SELFJOINS_IN_WHERE universe parameter is set to "Yes", both self-restrictions are placed in WHERE clause (see the screenshot below). This SQL will return less data than expected.

5. When SELFJOINS_IN_WHERE universe parameter is set to "No", both self_restrictions are placed in FROM clause join condition (see the screenshot below). This SQL will return more data than expected.

The correct SQL should look like the following:

This is "by design".

The Enhancement Request №3134 has been created on Idea Place.

SELFJOINS_IN_WHERE, ANSI, universe, designer, self-join, self-restricted, where, clause, condition , KBA , BI-BIP-SL , Semantic Layer , Problem