Creando View con UNION y index

Estoy creando una vista y tengo el siguiente problema

CREATE VIEW View4 WITH SCHEMABINDING
AS
SELECT ProductName, od.ProductID, SUM(od.UnitPrice*(1.00-Discount))
AS AvgPrice, SUM(od.Quantity) AS Units,
COUNT_BIG(*) AS Count
FROM dbo.[Order Details] AS od, dbo.Products AS p
WHERE od.ProductID = p.ProductID
AND od.UnitPrice > 10
GROUP BY ProductName, od.ProductID
union all
SELECT ProductName, od.ProductID, SUM(od.UnitPrice*(1.00-Discount))
AS AvgPrice, SUM(od.Quantity) AS Units,
COUNT_BIG(*) AS Count
FROM dbo.[Order Details] AS od, dbo.Products AS p
WHERE od.ProductID = p.ProductID
AND od.UnitPrice = ZZZ
GROUP BY ProductName, od.ProductID

GO
CREATE UNIQUE CLUSTERED INDEX VdiscountInd
ON View4 (ProductName, ProductID)

el error es el siguiente:

Msg 10116, Level 16, State 1, Line 4

Cannot create index on view
'CDW_DM_STAR_APORI.dbo.dim_ReportProvider_ixv' because it contains one
or more UNION, INTERSECT, or EXCEPT operators. Consider creating a
separate indexed view for each query that is an input to the UNION,
INTERSECT, or EXCEPT operators of the original view.

Hola, usted ha leido en sus libros sobre vistas indexadas y sus
limitaciones? si accede a ellos vera que no se permiten ese tipo de
sentencias como esta usted intentando hacer


Microsoft MVP SQLServer
www.sqltotalconsulting.com
-

"Oilers" escribió en el mensaje de
noticias:

Estoy creando una vista y tengo el siguiente problema

CREATE VIEW View4 WITH SCHEMABINDING
AS
SELECT ProductName, od.ProductID, SUM(od.UnitPrice*(1.00-Discount))
AS AvgPrice, SUM(od.Quantity) AS Units,
COUNT_BIG(*) AS Count
FROM dbo.[Order Details] AS od, dbo.Products AS p
WHERE od.ProductID = p.ProductID
AND od.UnitPrice > 10
GROUP BY ProductName, od.ProductID
union all
SELECT ProductName, od.ProductID, SUM(od.UnitPrice*(1.00-Discount))
AS AvgPrice, SUM(od.Quantity) AS Units,
COUNT_BIG(*) AS Count
FROM dbo.[Order Details] AS od, dbo.Products AS p
WHERE od.ProductID = p.ProductID
AND od.UnitPrice = ZZZ
GROUP BY ProductName, od.ProductID

GO
CREATE UNIQUE CLUSTERED INDEX VdiscountInd
ON View4 (ProductName, ProductID)

el error es el siguiente:

Msg 10116, Level 16, State 1, Line 4

Cannot create index on view
'CDW_DM_STAR_APORI.dbo.dim_ReportProvider_ixv' because it contains one
or more UNION, INTERSECT, or EXCEPT operators. Consider creating a
separate indexed view for each query that is an input to the UNION,
INTERSECT, or EXCEPT operators of the original view.

<Cannot create index on view
'CDW_DM_STAR_APORI.dbo.dim_ReportProvider_ixv' because it contains one or
more ***UNION*** ...>

De hecho, ni siquiera hace falta leer la ayuda. El mensaje de error ya lo
dice todo...
;-)

Saludos,
Alex


"Maxi Accotto" escribió en el mensaje
news:eRVoAD%

Hola, usted ha leido en sus libros sobre vistas indexadas y sus
limitaciones? si accede a ellos vera que no se permiten ese tipo de
sentencias como esta usted intentando hacer


Microsoft MVP SQLServer
www.sqltotalconsulting.com
-

"Oilers" escribió en el mensaje de
noticias:

Estoy creando una vista y tengo el siguiente problema

CREATE VIEW View4 WITH SCHEMABINDING
AS
SELECT ProductName, od.ProductID, SUM(od.UnitPrice*(1.00-Discount))
AS AvgPrice, SUM(od.Quantity) AS Units,
COUNT_BIG(*) AS Count
FROM dbo.[Order Details] AS od, dbo.Products AS p
WHERE od.ProductID = p.ProductID
AND od.UnitPrice > 10
GROUP BY ProductName, od.ProductID
union all
SELECT ProductName, od.ProductID, SUM(od.UnitPrice*(1.00-Discount))
AS AvgPrice, SUM(od.Quantity) AS Units,
COUNT_BIG(*) AS Count
FROM dbo.[Order Details] AS od, dbo.Products AS p
WHERE od.ProductID = p.ProductID
AND od.UnitPrice = ZZZ
GROUP BY ProductName, od.ProductID

GO
CREATE UNIQUE CLUSTERED INDEX VdiscountInd
ON View4 (ProductName, ProductID)

el error es el siguiente:

Msg 10116, Level 16, State 1, Line 4

Cannot create index on view
'CDW_DM_STAR_APORI.dbo.dim_ReportProvider_ixv' because it contains one
or more UNION, INTERSECT, or EXCEPT operators. Consider creating a
separate indexed view for each query that is an input to the UNION,
INTERSECT, or EXCEPT operators of the original view.