Hi,
I am in a process of creating Mview where I also want the WEEK_START_DATE to be part of the mview structure
The query for the mview is :
select region,subregion,low_risk,week_start_date,DECODE(REGION,'NA TAG - HQ',NULL,1) AS ORD,WEEK_START_DATE from
(select A.REGION,A.SUBREGION,A.WEEK_START_DATE,sum(nvl(A.LOW_RISK,0)) LOW_RISK
from
(select distinct lob2 region,lob3 subregion,
CASE
when WD.INDICATOR ='J' THEN sum(WD.WAR_AMOUNT_LABOR)
END AS "LOW_RISK"
from war_detail WD,PROJECT_MASTER PM,
COST_CENTER_HIERARCHY CCH
where WD.PROJECT_ID=PM.PROJECT_ID AND
PM.COST_CENTER=CCH.CCID AND SUBSTR(TO_WEEK(WD.WEEK_START_DATE),1,INSTR(TO_WEEK(WD.WEEK_START_DATE),' ',-1)-1)='FY'||SUBSTR(cch.YEAR,3,4)||' Q'||cch.QUARTER AND
SUBSTR(TO_WEEK(WD.WEEK_START_DATE),1,INSTR(TO_WEEK(WD.WEEK_START_DATE),' ',-1)-1)='FY'||SUBSTR(pm.YEAR,3,4)||' Q'||pm.QUARTER
group by lob2,lob3,indicator order by lob2 ,lob3 ) a
group by A.REGION,A.SUBREGION) order by ORD,region,subregion
the above erros out with "ORA-00904: "A"."WEEK_START_DATE": invalid identifier". WEEK_START_DATE field is part of war_detail table.
if I remove the WEEK_START_DATE from the query in the select part the mview gets created.
================================================================
The below code works fine but we also need the WEEK_START_DATE to be part of mview.
create materialized view report_low
refresh on demand start with sysdate next sysdate + 1/24 as
select region,subregion,low_risk,DECODE(REGION,'NA TAG - HQ',NULL,1) AS ORD from
(select A.REGION,A.SUBREGION,sum(nvl(A.LOW_RISK,0)) LOW_RISK
from
(select distinct lob2 region,lob3 subregion,
CASE
when WD.INDICATOR ='J' THEN sum(WD.WAR_AMOUNT_LABOR)
END AS "LOW_RISK"
from war_detail WD,PROJECT_MASTER PM,
COST_CENTER_HIERARCHY CCH
where WD.PROJECT_ID=PM.PROJECT_ID AND
PM.COST_CENTER=CCH.CCID AND SUBSTR(TO_WEEK(WD.WEEK_START_DATE),1,INSTR(TO_WEEK(WD.WEEK_START_DATE),' ',-1)-1)='FY'||SUBSTR(cch.YEAR,3,4)||' Q'||cch.QUARTER AND
SUBSTR(TO_WEEK(WD.WEEK_START_DATE),1,INSTR(TO_WEEK(WD.WEEK_START_DATE),' ',-1)-1)='FY'||SUBSTR(pm.YEAR,3,4)||' Q'||pm.QUARTER
group by lob2,lob3,indicator order by lob2 ,lob3 ) a
group by A.REGION,A.SUBREGION) order by ORD,region,subregion
Please help me on this as I am a newbie in pl/sql
Appreciate your help.