No "Group By" in generated sql code.
806113Jun 10 2012 — edited Jun 13 2012Hi.
I created very simple analyses.
Fact : Bunker Price,
Dimension : Day
Below is Logical SQL Code on Analyses.
----------------------------------------------------------------------------------
SELECT
0 s_0,
"8.Market Information"."Bunker Price"."Day Code" s_1,
"8.Market Information"."Day"."Month Code" s_2,
"8.Market Information"."Bunker Price"."Price" s_3
FROM "8.Market Information"
WHERE
("Day"."Month Code" = '201206')
ORDER BY 1, 3 ASC NULLS LAST, 2 ASC NULLS LAST
FETCH FIRST 100001 ROWS ONLY
----------------------------------------------------------------------------------
But it's real sql code has not "group by month_code, day_code" clause.
----------------------------------------------------------------------------------
WITH
SAWITH0 AS (select distinct T28235.DAY_CODE as c1,
T6151.MONTH_CODE as c2
from
DW111D T6151,
DW331F T28235
where ( T6151.DAY_CODE = T28235.DAY_CODE and T6151.MONTH_CODE = '201206' ) ),
SAWITH1 AS (select sum(T28235.PRICE) as c1
from
DW111D T6151,
DW331F T28235
where ( T6151.DAY_CODE = T28235.DAY_CODE and T6151.MONTH_CODE = '201206' ) )
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select 0 as c1,
D1.c1 as c2,
D1.c2 as c3,
D2.c1 as c4
from
SAWITH0 D1,
SAWITH1 D2
order by c3, c2 ) D1 where rownum <= 100001
----------------------------------------------------------------------------------
I already created a physical join
----------------------------------------------------------------------------------
"xxxx".""."yyyy"."DW111D"."DAY_CODE" = "xxxx".""."yyyy"."DW331F"."DAY_CODE"
----------------------------------------------------------------------------------
Below is "Table" View.
----------------------------------------------------------------------------------
Day Code Price
20120601 3,352,494
20120604 3,352,494
20120605 3,352,494
20120606 3,352,494
20120607 3,352,494
20120608 3,352,494
----------------------------------------------------------------------------------
I don't know why, Give me a hint even if tiny.
Thanks in advance.
Platform : Linux x86,
OBIEE : Version 11.1.1.6.2
Edited by: vingorius on Jun 10, 2012 6:23 PM