Creating Indexed View - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Creating Indexed View

Author

Topic

miranwar
Posting Yak Master

125 Posts

Posted - 2003-03-04 : 12:20:13

Hi Can Somebody help me I am trying to create an indexed view. One of the requirements is that the view sould be Schemabindinding. This is the SQl i am running:

Create view prVBoothSummary (iOwnerId,
iOrderNumber,
iOrderLineNumber,
chRevTypeCd,
chRevItemCd,
Area,
Length,
Width,
Opensides,--FE added to include opensides
OrderStatus,
OrderDate,
chShowCd,
iShowYear,
Totalprice,
Taxes,
Baseprice,
Unitprice,
iContactId,
chSourceCd
) WITH SCHEMABINDING as

select
OH.iOwnerId,
OH.iOrderNumber,
OD.iOrderLineNumber,
OD.chRevTypeCd,
OD.chRevItemCd,
OD.iQtyOrdered,
ODA1.vchAttributeValue,
ODA2.vchAttributeValue,
ODA3.vchAttributeValue,
OD.chOrderlineStatus,
OH.dtInsertDate,
OH.chShowCd,
OH.iShowYear,
OD.mTotalPrice,
OD.mTaxTotal,
OD.mBasePrice,
OD.mUnitPrice,
I.iindividualId,
OH.chSourceCd
from omOrderHeader
omOrderDetail OD,
omOrderDetailAttributes ODA1,
omOrderDetailAttributes ODA2,
--Added to include opensides
omOrderDetailAttributes ODA3,
omRevType RT,
Individual I

where OH.iOrderNumber = OD.iOrderNumber and
OD.chRevTypeCd = RT.chRevTypeCd and
RT.bBoothFlag = 1 and
ODA1.iOrderNumber = OD.iOrderNumber and
ODA1.iOrderLineNumber = OD.iOrderLineNumber and
ODA1.chAttributeCd = RT.chBoothLengthAttribute and
ODA2.iOrderNumber = OD.iOrderNumber and
ODA2.iOrderLineNumber = OD.iOrderLineNumber and
ODA2.chAttributeCd = RT.chBoothWidthAttribute and
--Added to include opensides
ODA3.iOrderNumber = OD.iOrderNumber and
ODA3.iOrderLineNumber = OD.iOrderLineNumber and
ODA3.chAttributeCd not in ('Hall','length','Width','Location','Pavilion') and
OH.isignerid *= I.iindividualid

However i am geeting the following error message

Server: Msg 4512, Level 16, State 3, Procedure prVBoothSummary, Line 39
Cannot schema bind view 'prVBoothSummary' because name 'omOrderHeader' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Can anybody help

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-04 : 12:25:59

one part name ....
authors

two part name ...
dbo.authors

three part name ...
pubs.dbo.authors

four part name ....
mydb_server.pubs.dbo.authors

Jay White
{0}

miranwar
Posting Yak Master

125 Posts

Posted - 2003-03-04 : 12:36:22

Sorry one more thing am i right to belive that an indexe view cannot contain a table that is joined to itself

miranwar
Posting Yak Master

125 Posts

Posted - 2003-03-04 : 12:39:35

thanks Jay...
If i alwyas have to have a two partname then i cannot create a view that contains alias tables? if u look from my sql i have three alias;s of omorderdetailattributes table

Regards,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources