In a previous article I showed the basics of working with OBIEE Outerjoins: (http://obiee101.blogspot.com/2008/11/obiee-understanding-outerjoins-part-1.html).
This works fine until you add a filter :
From the log:
select T26.D_YEAR as c1,
sum(T31.F_FACT_VAL) as c2
from
DIM_YEAR_MONTH_DAY T26 left outer join
F_FACTS T31 On T26.D_YEAR_MONTH_DAY = T31.D_DATE
group by T26.D_YEAR
having 440000 < sum(T31.F_FACT_VAL)
order by c1
This "kills" the outerjoin.... I hear think why not add "OR IS NULL". Let's do that:
From the log:
select T26.D_YEAR as c1,
sum(T31.F_FACT_VAL) as c2
from
DIM_YEAR_MONTH_DAY T26 left outer join
F_FACTS T31 On T26.D_YEAR_MONTH_DAY = T31.D_DATE
group by T26.D_YEAR
having 440000 < sum(T31.F_FACT_VAL) or sum(T31.F_FACT_VAL) is null
order by c1
We still are missing a couple off years.
Let try an in view filter: change F_FACT_VAL to:
CASE WHEN SUM(F_FACTS.F_FACT_VAL by DIM_YEAR_MONTH_DAY.D_YEAR )> 440000 then SUM(F_FACTS.F_FACT_VAL by DIM_YEAR_MONTH_DAY.D_YEAR ) else NULL end
This give us all the years back:
From the log:
SET VARIABLE QUERY_SRC_CD='Report';SELECT DIM_YEAR_MONTH_DAY.D_YEAR saw_0, CASE WHEN SUM(F_FACTS.F_FACT_VAL by DIM_YEAR_MONTH_DAY.D_YEAR )> 440000 then SUM(F_FACTS.F_FACT_VAL by DIM_YEAR_MONTH_DAY.D_YEAR ) else NULL end saw_1 FROM BM_OUTER_JOIN ORDER BY saw_0
select T26.D_YEAR as c1,
sum(T31.F_FACT_VAL) as c4
from
DIM_YEAR_MONTH_DAY T26 left outer join
F_FACTS T31 On T26.D_YEAR_MONTH_DAY = T31.D_DATE
group by T26.D_YEAR
order by c1
As you can see OBIEE did the case when logic internally.
Till Next Time
This article is orignally written for the ciber knowledge blog:
No comments:
Post a Comment