1576244 - Conditional placement of self-restricting joins.

SAP Knowledge Base Article - Public

1576244 - Conditional placement of self-restricting joins.

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

club_unv.JPG

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.

SELFJOINS_IN_WHERE.JPG

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.

SELFJOINS_NOT_IN_WHERE.JPG

The correct SQL should look like the following:

SELECT DISTINCT
Service.service,
Service_Line.service_line
FROM
Service_Line RIGHT OUTER JOIN Service ON
(Service.sl_id=Service_Line.sl_id AND Service_Line.resort_id='1.0')
WHERE
(Service.service='Accomodation')

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

Product

SAP BusinessObjects Xcelsius Enterprise 2008