3 Replies Latest reply on Jul 6, 2018 9:06 AM by Manoj Dixit

    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: Dimensional
      SELECT DISTINCT DimLOB.L1CU001 AS c1
      , FactTab.CURRENT_FLAG AS c2
      FROM LOB DimLOB
      , USR DimUsr
      LEFT OUTER JOIN (PRIV TabPriv
      INNER JOIN (BUSAREA DimBA
      INNER JOIN (DTE DimLDate
      INNER JOIN FACT01 FactTab
      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_ID
      WHERE (FactTab.WORKITEM_ID = DimLOB.WORKITEM_ID
      AND FactTab.CURRENT_FLAG  = 1
      AND DimLOB.CURRENT_FLAG   = 1
      AND 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 Dimensional
      SELECT DISTINCT DimLOB.L1CU001 AS c1
      , FactTab.CURRENT_FLAG AS c2
      FROM USR DimUsr
      LEFT OUTER JOIN (PRIV TabPriv
      INNER JOIN (BUSAREA DimBA
      INNER JOIN (DTE DimLDate
      INNER JOIN (LOB DimLOB
      INNER JOIN FACT01 FactTab
      ON 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_ID
      WHERE (FactTab.CURRENT_FLAG = 1
      AND DimLOB.CURRENT_FLAG    = 1
      AND 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.