When would a join condition be part of WHERE clause?

Manoj Dixit

Hi,

We are on OBI EE 10g (10.1.3.4.2) (agree very old, upgrades not within my capacity).

I have two business models: 'Dimensional' and 'Custom Dimensional'. I am manually trying to replicate the 'Dimensional' business model hence the existence of the 'Custom Dimensional' business model.

The logical **Star** is:

Fact joined to DimBA, DimDate, DimLOB, DimUser where BA is Business Area and LOB is Line of Business. **DimBA is joined to TabPriv logical table.**

All the joins are equi-joins except a join between the Fact LT and DimUser LT in which case the DimUser is the driving table and the join type is Left Outer.

I have re-checked again and again, the logical joins and they are alike in the two business models.

The issue is a query based on subject area 'Dimensional' generates a SQL which joins the Fact and the DimLOB within the WHERE clause of the SQL query.

--Presentation: DimensionalSELECT DISTINCT DimLOB.L1CU001 AS c1, FactTab.CURRENT_FLAG AS c2FROM LOB DimLOB, USR DimUsrLEFT OUTER JOIN (PRIV TabPrivINNER JOIN (BUSAREA DimBAINNER JOIN (DTE DimLDateINNER JOIN FACT01 FactTabON DimLDate.DATE_ID        = FactTab.EVENT_DATE_ID)ON DimBA.BAWT_ID           = FactTab.BAWT_ID)ON DimBA.UNITCD            = TabPriv.UNITCD AND DimBA.WRKTYPE = TabPriv.PRVTYPE)ON DimUsr.USER_ID          = FactTab.LOCKED_USER_IDWHERE (FactTab.WORKITEM_ID = DimLOB.WORKITEM_IDAND FactTab.CURRENT_FLAG  = 1AND DimLOB.CURRENT_FLAG   = 1AND TabPriv.USERID        = 'MANOJ'AND TabPriv.RECORDCD      = 'W'AND TabPriv.VIEWFLAG      = 'Y'AND (DimUsr.USERID       IN ('MANOJ'))AND DimLDate.DTE BETWEEN (TO_DATE ('2018-07-04', 'YYYY-MM-DD') + - 7) AND TO_DATE ('2018-07-04', 'YYYY-MM-DD'))ORDER BY c1, c2;

Whereas, another query based on subject area 'Custom Dimensional' generates a SQL which joins the Fact and the DimLOB within the FROM clause of the SQL query.

-- Presentation: Custom DimensionalSELECT DISTINCT DimLOB.L1CU001 AS c1, FactTab.CURRENT_FLAG AS c2FROM USR DimUsrLEFT OUTER JOIN (PRIV TabPrivINNER JOIN (BUSAREA DimBAINNER JOIN (DTE DimLDateINNER JOIN (LOB DimLOBINNER JOIN FACT01 FactTabON FactTab.WORKITEM_ID      = DimLOB.WORKITEM_ID)
ON DimLDate.DATE_ID         = FactTab.EVENT_DATE_ID)ON DimBA.BAWT_ID            = FactTab.BAWT_ID)ON DimBA.UNITCD             = TabPriv.UNITCD AND DimBA.WRKTYPE = TabPriv.PRVTYPE)ON DimUsr.USER_ID           = FactTab.LOCKED_USER_IDWHERE (FactTab.CURRENT_FLAG = 1AND DimLOB.CURRENT_FLAG    = 1AND TabPriv.USERID         = 'MANOJ'AND TabPriv.RECORDCD       = 'W'AND TabPriv.VIEWFLAG       = 'Y'AND (DimUsr.USERID        IN ('MANOJ'))AND DimLDate.DTE BETWEEN (TO_DATE ('2018-07-04', 'YYYY-MM-DD') + - 7) AND TO_DATE ('2018-07-04', 'YYYY-MM-DD'))ORDER BY c1, c2;

I have not observed differences in the query results as this is in development environment only. I wouldn't want to see differences in live environment so I am trying to get pointers / guidelines to investigate the cause of the differences.

Please note that the 'Dimensional' business model is an out of box, pre-designed and developed / configured by someone else. Already some Answers have been in production based on this business model and it's corresponding subject area. We are trying to enhance the subject area from end user's perspective so, replication. There are no dimensions and hierarchies in the 'Dimensional' business model, however, I have introduced them in the 'Custom Dimensional' business model.

It would be nice to know where the issue could be.

Thanks and regards,

Manoj.

Manoj Dixit

Compared the output in SQL Developer this morning in the live environment. They match. However, the queries (particularly the FROM and WHERE clauses) generated are different (and have different meanings). I tend to feel the second one is more correct syntax-wise, as the fact is joined to other dimensions (with snowflake) based on inner joins but the user dimension is joined based on left outer.

Martin van Donselaar

Logically the queries are the same so they will always get the same results, I wouldn't worry about it to much.

Manoj Dixit

Thanks for the reply Martin. For peace of mind, I had even removed the filters on USERID and DTE columns.

Regards,

Manoj.