Solved

Error "It contains one or more disallowed constructs" On SQL View and Clustered Index

Posted on 2004-09-29
7
2,355 Views
Last Modified: 2013-11-15
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.company,contact1.title,contact1.contact,
contact1.phone1 as direct,contact1.phone2 as switch,contact1.phone3 as Mobile,contact1.fax,contact1.ext3,contact1.key1,
contact1.key2,contact1.key3,contact1.key4,contact1.key5,contact1.mergecodes,conts.contsupref as email
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
0
Comment
Question by:Squeel
7 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 12177647
Try whithout the sub-select as follows

CREATE VIEW 1EMAIL
WITh SCHEMABINDING
AS
select '1' as sortby,contact1.accountno,contact1.company,contact1.title,contact1.contact,
contact1.phone1 as direct,contact1.phone2 as switch,contact1.phone3 as Mobile,contact1.fax,contact1.ext3,contact1.key1,
contact1.key2,contact1.key3,contact1.key4,contact1.key5,contact1.mergecodes,conts.contsupref as email
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%'

0
 

Author Comment

by:Squeel
ID: 12177874
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.company,contact1.title,contact1.contact,
contact1.phone1 as direct,contact1.phone2 as switch,contact1.phone3 as Mobile,contact1.fax,contact1.ext3,contact1.key1,
contact1.key2,contact1.key3,contact1.key4,contact1.key5,contact1.mergecodes,conts.contsupref as email
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
0
 
LVL 12

Expert Comment

by:patrikt
ID: 12177968
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

0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 12

Expert Comment

by:patrikt
ID: 12177982
Oops.
I owerlooked that Outer Join is disallowed. There is a problem.

Probably there is way with Inned join.

Patrik
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 12178527
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%'

0
 

Author Comment

by:Squeel
ID: 12179146
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?
0
 
LVL 12

Accepted Solution

by:
patrikt earned 125 total points
ID: 12179396
Your last suggestion is probably faster solution.

Outer joins are not indexable becouse of possible nulls on one side. It leads sqlserver to nondeterministic solution which is not indexable.
If you separate both parts you will add two indexes and sql will chose which one (or both) to use.

Patrik
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Help - MSSQL - Averages 5 27
Flattening heirachies 3 31
Connecting to multiple databases to create a Dashboard 5 26
Job - date manual 1 23
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question