Materialized View

In this forum you can share stuff related to Oracle 11g, 10g, 9i.
Post Reply
haseebkhatri
Posts: 18
Joined: Fri Sep 19, 2008 1:11 am
Location: Pakistan

Materialized View

Post by haseebkhatri »

CREATE MATERIALIZED VIEW MV_SALE_TERR_WISE
AS
(Select Team_ID,Team_Name,ZonalCode,RegionalCode,TERRCODE,P_No,Assc_Name,Idate,PordId,
ProdDesc,ProductID,Product,
Sum(NVL(RUnits,0))+Sum(NVL(FUnits,0)) RUnits,Sum(NVL(WUnits,0)) WUnits,Sum(NVL(CUnits,0)) CUnits,
Sum(NVL(IUnits,0)) IUnits,
(Sum(NVL(RUnits,0)) + Sum(NVL(FUnits,0)) + Sum(NVL(CUnits,0))+ Sum(NVL(IUnits,0))+
Sum(NVL(WUnits,0))) "Total Units",
Sum(NVL(TgtQty,0)) "Target Units",
TP,
((Sum(NVL(RUnits,0)) +Sum(NVL(FUnits,0)) + Sum(NVL(CUnits,0))+ Sum(NVL(IUnits,0))+
Sum(NVL(WUnits,0))) * TP) "Total Value",
Sum(NVL(TgtValue,0)) "Target Value"
From
(
(select Team_ID,Team_Name,ZonalCode,RegionalCode,TERRCODE,P_No,Assc_Name,InvoiceDate Idate,
PordID,ProdDesc,ProductID,Product,TP,
CASE WHEN TEAM_ID IN (1,4,7,5,6,12,17) AND CHAIN='R' THEN
sum(NVL(SUMQty,0))
END RUNITS,
CASE WHEN TEAM_ID IN (1,4,7,5) AND CHAIN='F' THEN
sum(NVL(SUMQty,0))
END FUNITS,
Per RShare, Null CShare, Null CUnits,
CASE WHEN TEAM_ID IN (1,4,7,5,6,12,17) AND CHAIN='I' THEN
sum(NVL(SUMQty,0))
END IUNITS,
CASE WHEN TEAM_ID IN (1,4,7,5,6,12,17) AND CHAIN='W' THEN
sum(NVL(SUMQty,0))
END WUNITS,
Null TgtQty,
Null TgtValue,Null Ach
from SCP.IMSTerrSale
where
TEAM_ID IN (1,4,7,5,6,12,17)
group by Team_ID,Team_Name,ZonalCode,RegionalCode,TERRCODE,P_No,Assc_Name,InvoiceDate,
PordId,ProdDesc,ProductID,Product,TP,Per,Chain)
Union
(select Team_ID,Team_Name,ZonalCode,RegionalCode,TERRCODE,P_No,Assc_Name,InvoiceDate Idate,
PordId,ProdDesc,ProductID,Product,TP,Null RUnits,Null FUnits,Null RShare,Per CShare,
Sum(Per_Units) CUnits,Null IUnits,Null WUnits,
Null TgtQty,Null TgtValue,Null Ach
from SCP.IMSTERRSALECHAIN
where
TEAM_ID IN (1,4,7,5,6,12,17)
group by Team_ID,Team_Name,ZonalCode,RegionalCode,TERRCODE,P_No,Assc_Name,InvoiceDate,
PordId,ProdDesc,ProductID,Product,TP,Per,Chain)
UNION
(
select a.TeamId TeamID,b.Team_Name Team_Name,b.ZonalCode,b.RegionalCode,a.TerrCode TerrCode,
b.P_No,b.Assc_Name Assc_Name,a.mnth Idate,c.PordiD,d.ProdDesc,c.ProductID,c.Product,c.TP,
Null RUnits,Null FUnits,Null RShare,Null CShare,
Null CUnits,Null IUnits,Null WUnits,sum(nvl(a.TgtQty,0)) TgtQty,sum(nvl(a.TgtValue,0)) TgtValue,
Null Ach
from SCP.TerrTgt a, SCP.Terrmst b, SCP.Product_History c,SCP.AvenProd d
where a.TeamID = b.Team_Id And a.Asscno = b.Assc_No And a.TerrCode = b.TerrCode
And a.ProductID = c.ProductID
And a.TeamID = c.Team_ID
And a.TeamID = d.Team_ID
And c.Team_ID= d.Team_ID
And d.ProdID = c.PordID
AND a.TEAMID IN (1,4,7,5,6,12,17)
And c.Active = 'Y'
group by
a.TeamID,b.Team_Name,b.ZonalCode,b.RegionalCode,a.TERRCODE,b.P_No,b.Assc_Name,a.mnth,
c.PordID,d.ProdDesc,c.ProductID,c.Product,c.TP))
Group By Team_ID,Team_Name,ZonalCode,RegionalCode,TERRCODE,P_No,Assc_Name,Idate,
PordId,
ProdDesc,ProductID,Product,TP)



I'm create materialized view from the above query

Following Error Occur
<b>from SCP.IMSTerrSale
*
ERROR at line 33:
ORA-22818: subquery expressions not allowed here</b>


Kindly reply me its urgent.

Regards
Abdul Haseeb
Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest