Symptom
- 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.
Environment
- SAP BusinessObjects XI 3.1
- Universe Designer
Reproducing the Issue
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:
SELECT DISTINCT |
Cause
This is "by design".
Resolution
The Enhancement Request №3134 has been created on Idea Place.
Keywords
SELFJOINS_IN_WHERE, ANSI, universe, designer, self-join, self-restricted, where, clause, condition , KBA , BI-BIP-SL , Semantic Layer , Problem