Indexed View Issue (Self Join Error)

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 2005 Forums

Transact-SQL (2005)

Indexed View Issue (Self Join Error)

Author

Topic

sassy
Starting Member

4 Posts

Posted - 2008-11-05 : 15:53:18

Hello All,

Please bare with me as this is my first post so I will try to give as much information as possible.

I am trying to set up a unique clustered index on a view that I created. The view does not actually contain "self joins" but there are a series of inner joins including some that use the same table. For example, this is the part that is causing the problem:


	dbo.tbl_Value Val1 ON Val1.ValID = tbl_Example.Column1
		INNER JOIN
	dbo.tbl_Value Val2 ON Val2.ValID = tbl_Example.Column2
		INNER JOIN
	dbo.tbl_Value Val3 ON Val3.ValID = tbl_Example.Column3

SQL Server is returning an error message "Index cannot be created on "viewname" because the view contains a self join on tbl_Value"

Does anyone have any ideas on how I can get around this issue?

Thanks so much for any replies.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-05 : 16:21:49

tbl_value is self join.

Please post your query.

sassy
Starting Member

4 Posts

Posted - 2008-11-06 : 11:00:29

Hello Again,

Well I would really like to post my original code but due to confidentiality agreement I can't. I tried to come up with an example of what I mean...I can't even run it but I think it shows what I mean.


ALTER VIEW [dbo].[vwCustomerDetail] WITH SCHEMABINDING AS 
SELECT CUS.typeID, 
	PRD.[Description] AS Product, 
	CUS.Fund,
	CUS.Status,
	CUS.Class,
	CUS.Code,
	COUNT_BIG(*) AS cBig
FROM 
	dbo.Customer CUS 
		INNER JOIN
	dbo.tbl_lkpCustomerType CTY ON CUS.typeID = CTY.typeID 
		INNER JOIN
	dbo.tblProduct PRD ON PRD.productID = CUS.Product 
		INNER JOIN
	dbo.tbl_lkpFund FND ON FND.FundID = CUS.Fund 
		INNER JOIN
	dbo.tbl_lkpValue LV1 ON LV1.LOVID = CUS.Status 
		INNER JOIN
	dbo.tbl_lkpValue LV2 ON LV2.LOVID = CUS.Class
		INNER JOIN
	dbo.tbl_lkpValue LV3 ON LV3.LOVID = CUS.Code
GROUP BY 
	CUS.typeID, PRD.[Description], CUS.Fund, CUS.Status, CUS.Class, CUS.Code, COUNT_BIG(*) AS cBig

The self joins on tbl_lkpValue are what is causing the problem. I thought this would work since they are named different aliases (LV1, LV2, LV3). I need to find a way to get around this so I can index this view.

Thanks for trying!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources