0 Replies Latest reply: Feb 11, 2013 11:50 AM by 833658 RSS

    Complex join and SQL

    833658
      Hello,

      I have three tables in the physical layer:

      SYSTEM_DIM (System Dimension)
      FEED_DIM (Feed Dimension)
      EXC_SUMMARY_FACT (Exception Fact)

      I have applied a complex join (as it is SCD type 2) as shown below

      Between System Dimension and Exception Fact
      SYSTEM_DIM.SYSTEM_ID = EXC_SUMMARY_FACT.SOURCE_SYSTEM_ID
      AND EXC_SUMMARY_FACT.COB_DATE BETWEEN SYSTEM_DIM.SYSTEM_EFF_DATE AND SYSTEM_DIM.SYSTEM_END_DATE
      and Between Feed Dimension and Exception Fact
      FEED_DIM.FEED_ID = EXC_SUMMARY_FACT.TARGET_FEED_ID
      AND EXC_SUMMARY_FACT.COB_DATE BETWEEN FEED_DIM.FEED_EFF_DTIME AND FEED_DIM.FEED_END_DTIME
      Now, when I take one column from each of the dimensions and fact, the SQL generated is weird (see the bold part)
      select T88049.FEED_NAME as c1,
      T88106.SYSTEM_NAME as c2,
      sum(T88169.EXCEPTION_COUNT) as c3
      from
      FEED_DIM_MV T88049 /* FEED_DIM */ ,
      SYSTEM_DIM_MV T88106 /* SYSTEM_DIM */ ,
      EXC_SUMMARY_FACT T88169 /* EXC_SUMMARY_FACT */
      where ( T88049.FEED_ID = T88169.TARGET_FEED_ID and T88106.SYSTEM_ID = T88169.SOURCE_SYSTEM_ID and T88106.SYSTEM_END_DATE >= T88169.COB_DATE and T88169.COB_DATE >= T88049.FEED_EFF_DTIME and T88169.COB_DATE between >T88106.SYSTEM_EFF_DATE and T88049.FEED_END_DTIME )
      group by T88049.FEED_NAME, T88106.SYSTEM_NAME
      order by c1, c2
      As you can see, the SQL has messed the between operator. Is it a known bug in OBIEE?
      has anybody come across this scenario?

      (We are using OBIEE 10.1.3.4.1)