asked on

create indexed view error

I have this error when creating indexed view:
error:
Msg 10109, Level 16, State 1, Line 1
Cannot create index on view "REFAC_DATA.dbo.VW_ScoreCard" because it references derived table "sal" (defined by SELECT statement in FROM clause). Consider removing the reference to the derived table or not indexing the view.

here my view:
CREATE VIEW dbo.v
WITH SCHEMABINDING
AS
SELECT
y.c1, X.c2
FROM
(SELECT
1 AS c1) y
INNER MERGE JOIN
(SELECT
1 AS c2) X ON y.c1 = x.c2;
GO

error executing:
CREATE UNIQUE CLUSTERED INDEX cix_v
ON dbo.v (c1);
GO

knightEknight

What happens if you make views out of the two derived tables and then use them in the view "v" ? (create with schemabinding)

abgconsulting

ASKER

Thanks for your reply.
This was just an example.
LEt's go with:

CREATE VIEW dbo.v
WITH SCHEMABINDING
AS
SELECT
y.c1, X.c2
FROM
(SELECT
c1 from table1) y
INNER MERGE JOIN
(SELECT
c2 from table2) X ON y.c1 = x.c2;
GO

error executing:
CREATE UNIQUE CLUSTERED INDEX cix_v
ON dbo.v (c1);
GO

Do you explain more what you mean?

Thanks,

Alioune.

knightEknight

Let's table my first proposal for the moment ... why don't you just use a straight join in the view "v", instead of the sub-queries?

CREATE VIEW dbo.v
WITH SCHEMABINDING
AS
SELECT y.c1, X.c2
FROM table1 y
INNER MERGE JOIN table2 X
ON y.c1 = X.c2;
GO

knightEknight

-- my original idea (which I don't know if this will work) is to create views out of the sub-queries:

CREATE VIEW dbo.v1
WITH SCHEMABINDING
AS
SELECT 1 AS c1
GO

CREATE VIEW dbo.v2
WITH SCHEMABINDING
AS
SELECT 1 AS c1
GO

-- then use these views in "v"

CREATE VIEW dbo.v
WITH SCHEMABINDING
AS
SELECT y.c1, X.c2
FROM dbo.v1 y
INNER MERGE JOIN dbo.v2 X
ON y.c1 = X.c2;
GO

knightEknight

oops, the second view of the three above should alias the value as c2 instead of c1

abgconsulting

ASKER

Ok,

but this was just a simplified query. My query is really more complex. My question is: it is possible to create indexed view using derived tables?
IS there a workaround about this error?

Thanks.

ASKER CERTIFIED SOLUTION

TempDBA

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer