cancel
Showing results for 
Search instead for 
Did you mean: 

Join Order in the BO query

Former Member
0 Kudos

I am using   SAP BO universe designer version 12.6.1.1691.

I use BO Infoview version 3.1 to create reports.

The scenario consists of a fact table, Fact_Table1 and 3 master tables MT1,MT2,MT3. The fact table is joined with Master table 1 and 2 on the respective Ids.

The 3rd master table  MT3 is  joined with MT1,MT2 using a complex join. The query generated in BO is of the form

select MT1.dim1,

        MT2.dim2,

        sum(FT1.measure1)

FROM   MT3 INNER JOIN MT2  ON (MT3.id1=MT2.id1 and MT3.id2=MT1.id2)

INER JOIN FT1  ON (FT1.id2=MT2.id2)

INNER JOIN MT1 ON (FT1.id1=MT1.id1)

group by 1,2

This throws  an error ' *** Failure 3782 Improper column reference in the search condition of a joined table. ' since the join in the first line refers a column from a table that is mentioned in the last line.

However this doesn't occur  if the order of joins is interchanged.

MT3 can't be joined with MT1 and MT2 with a simple join since this will create a loop.

Is there any way I can specify the order of joins in BO designer, except putting them in a context, to avoid this problem?

Is there an alternate way to obtain the above query without using complex joins?

Accepted Solutions (1)

Accepted Solutions (1)

former_member4998
Active Contributor
0 Kudos

Hi


MT3 is dimension table, so you can create alias table, so alias breaks a loop by using the same table twice in the same query.

Find the below links for more info

http://www.youtube.com/watch?v=ezss-VMjQRA

http://www.dagira.com/2009/07/22/context-versus-alias-overview/

Note: PLEASE mark blog as helpful & answered

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi,

You still have a loop in the universe:

MT1 ----- Fact ----- MT2

  |                              |

  |                              |

  ------------MT3--------

So you'd better solve the loop either by using contexts or aliases. Maybe MT3 can be aliased if it plays 2 different roles? Or one of the other 2 master tables (either MT1 or MT2) can be aliased? It all depends on your data model.