Squeel
asked on
Error "It contains one or more disallowed constructs" On SQL View and Clustered Index
Hi,
I am trying to create a clustered index on a view I am creating but it keep s retruening with
It contains one or more disallowed constructs
I have tried this with out a left outer join and it works. Yet when I add the left outer join on the end it doesn't work? I have seen in other areas on the net where people have managed to use a left outer join in the creation of view, yet I am still at a loss to whether I am doing anything wrong in the statement, or whether Left outer joins are a no go? Is it Possible? Here is the query etc i am using to create the View...any idea?
CREATE VIEW 1EMAIL
WITh SCHEMABINDING
AS
select '1' as sortby,contact1.accountno,
contact1.phone1 as direct,contact1.phone2 as switch,contact1.phone3 as Mobile,contact1.fax,contac
contact1.key2,contact1.key
from dbo.contact1 LEFT OUTER JOIN
(SELECT accountno, contsupref
FROM dbo.contsupp
WHERE zip LIKE '_1%' AND contsupp.contact LIKE 'E-mail%') as conts ON dbo.CONTACT1.ACCOUNTNO = conts.accountno
GO
CREATE UNIQUE CLUSTERED INDEX indClustered
ON GM.DBO.1EMAIL (accountno)
GO
I am trying to create a clustered index on a view I am creating but it keep s retruening with
It contains one or more disallowed constructs
I have tried this with out a left outer join and it works. Yet when I add the left outer join on the end it doesn't work? I have seen in other areas on the net where people have managed to use a left outer join in the creation of view, yet I am still at a loss to whether I am doing anything wrong in the statement, or whether Left outer joins are a no go? Is it Possible? Here is the query etc i am using to create the View...any idea?
CREATE VIEW 1EMAIL
WITh SCHEMABINDING
AS
select '1' as sortby,contact1.accountno,
contact1.phone1 as direct,contact1.phone2 as switch,contact1.phone3 as Mobile,contact1.fax,contac
contact1.key2,contact1.key
from dbo.contact1 LEFT OUTER JOIN
(SELECT accountno, contsupref
FROM dbo.contsupp
WHERE zip LIKE '_1%' AND contsupp.contact LIKE 'E-mail%') as conts ON dbo.CONTACT1.ACCOUNTNO = conts.accountno
GO
CREATE UNIQUE CLUSTERED INDEX indClustered
ON GM.DBO.1EMAIL (accountno)
GO
ASKER
Thanks, ok have tried what you have suggested but no joy I am afraid.
CREATE VIEW 1EMAIL
WITh SCHEMABINDING
AS
select '1' as sortby,contact1.accountno,
contact1.phone1 as direct,contact1.phone2 as switch,contact1.phone3 as Mobile,contact1.fax,contac
contact1.key2,contact1.key
from dbo.contact1
LEFT OUTER JOIN dbo.contsupp conts
ON dbo.CONTACT1.ACCOUNTNO = conts.accountno AND conts.zip LIKE '_1%' AND conts.contact LIKE 'E-mail%'
GO
CREATE UNIQUE CLUSTERED INDEX indClustered
ON GM.DBO.1EMAIL (accountno)
GO
--------------It is still coming back with ---------
Server: Msg 1936, Level 16, State 1, Line 1
Cannot index the view 'GM.dbo.EMAIL100'. It contains one or more disallowed constructs.
I can get the view created with SCHEMABINDING applied but It seems the indexing part is kicking up a fuss
CREATE VIEW 1EMAIL
WITh SCHEMABINDING
AS
select '1' as sortby,contact1.accountno,
contact1.phone1 as direct,contact1.phone2 as switch,contact1.phone3 as Mobile,contact1.fax,contac
contact1.key2,contact1.key
from dbo.contact1
LEFT OUTER JOIN dbo.contsupp conts
ON dbo.CONTACT1.ACCOUNTNO = conts.accountno AND conts.zip LIKE '_1%' AND conts.contact LIKE 'E-mail%'
GO
CREATE UNIQUE CLUSTERED INDEX indClustered
ON GM.DBO.1EMAIL (accountno)
GO
--------------It is still coming back with ---------
Server: Msg 1936, Level 16, State 1, Line 1
Cannot index the view 'GM.dbo.EMAIL100'. It contains one or more disallowed constructs.
I can get the view created with SCHEMABINDING applied but It seems the indexing part is kicking up a fuss
From InsideSQL:
-----------------
In addition to the requirement that all functions used in the view be deterministic and that the seven SET options be set to the appropriate values, the view definition can't contain any of the following:
TOP
text, ntext, or image columns
DISTINCT
MIN, MAX, COUNT(*), COUNT(<expression>), STDEV, VARIANCE, AVG
SUM on a nullable expression
A derived table
The ROWSET function
Another view (you can reference only base tables)
UNION
Subqueries, OUTER joins, or self-joins
Full-text predicates (CONTAINS, FREETEXT)
COMPUTE, COMPUTE BY
ORDER BY
-------
On first sight I could not decide if any of disalloowed constructs are used. But may be you can.
Try to go through this list, may be it helps.
Patrik
-----------------
In addition to the requirement that all functions used in the view be deterministic and that the seven SET options be set to the appropriate values, the view definition can't contain any of the following:
TOP
text, ntext, or image columns
DISTINCT
MIN, MAX, COUNT(*), COUNT(<expression>), STDEV, VARIANCE, AVG
SUM on a nullable expression
A derived table
The ROWSET function
Another view (you can reference only base tables)
UNION
Subqueries, OUTER joins, or self-joins
Full-text predicates (CONTAINS, FREETEXT)
COMPUTE, COMPUTE BY
ORDER BY
-------
On first sight I could not decide if any of disalloowed constructs are used. But may be you can.
Try to go through this list, may be it helps.
Patrik
Oops.
I owerlooked that Outer Join is disallowed. There is a problem.
Probably there is way with Inned join.
Patrik
I owerlooked that Outer Join is disallowed. There is a problem.
Probably there is way with Inned join.
Patrik
Could it be the refence to the unaliased table name in:
>contsupp.contact LIKE 'E-mail%'
That should perhaps be
cont.contact LIKE 'E-mail%'
>contsupp.contact LIKE 'E-mail%'
That should perhaps be
cont.contact LIKE 'E-mail%'
ASKER
Thanks Patrik I will try the inner join but dont think this will work.
In both cases the actual Views themselves work and also work as standard SQL expressions.
Thanks monosodiumg but already tried that in the second version of the View query?
Any other ideas?
Along way round but may have to create an indexed view for each part of the query then create a final view to draw the two seperate views together? Dont know if possible though?
In both cases the actual Views themselves work and also work as standard SQL expressions.
Thanks monosodiumg but already tried that in the second version of the View query?
Any other ideas?
Along way round but may have to create an indexed view for each part of the query then create a final view to draw the two seperate views together? Dont know if possible though?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
CREATE VIEW 1EMAIL
WITh SCHEMABINDING
AS
select '1' as sortby,contact1.accountno,
contact1.phone1 as direct,contact1.phone2 as switch,contact1.phone3 as Mobile,contact1.fax,contac
contact1.key2,contact1.key
from dbo.contact1
LEFT OUTER JOIN dbo.contsupp conts
ON dbo.CONTACT1.ACCOUNTNO = conts.accountno AND zip LIKE '_1%' AND contsupp.contact LIKE 'E-mail%'