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_DTIMEand 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?