Indexed View with LEFT JOIN
-
Hi all
The following is the DDL for a view that I am creating. I want to be able to create an index on this view but, due to the LEFT JOIN's I can't.
Anybody got any ideas on how I can present the same resultset in an indexed view?
Before anyone suggest a stored proc, we can't use them due to a architectural design decision. All data from the database must be presented in a format that can be accessed standard SQL statments.
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
/****** Object: View dbo.VIEW1 Script Date: 6/09/2002 10:38:14 AM ******/
if exists (select * from sysobjects where id = object_id('dbo.Ivw_Dim_Account') and sysstat & 0xf = 2)
drop view dbo.Ivw_Dim_Account
GO
CREATE VIEW dbo.Ivw_Dim_Account WITH SCHEMABINDING
AS
SELECT
Acct.KY_POST_MAINT, Acct.ID_CNTRY_PROD, Acct.C_TYPE_CHNL
, Acct.D_EXPRY_R_FXD, Acct.D_STRT_R_FXD, Acct.F_R_FXD, Acct.D_CHNG_OD_LAST, Acct.D_CRE_OD
, Acct.A_LMT_TEMP, Acct.A_LMT_PERM, Acct.D_CLSE, Acct.D_OPEN, Acct.TERM, Acct.N_SBCH_DMCL
, Acct.N_CAP_LOB, Acct.C_SI, Acct.C_RG, Acct.N_OU_CLG, Acct.N_OU_CUST, Acct.N_OU
, Acct.DS_ACCT, Acct.D_KY_END, Acct.D_KY_STRT, Acct.ID_CNTRY, Acct.N_ACCT, Acct.KY_ACCT
, Anzsic.C_ASIC, Anzsic.DS_ASIC
, Distr.KY_DISTR, Distr.ID_DISTR, Distr.D_KY_STRT AS D_KY_STRT_DISTR
, Distr.D_KY_END AS D_KY_END_DISTR, Distr.DS_DISTR, Distr.NM_DISTR_HLD
, BCtr.KY_BUS_CTR, BCtr.ID_BUS_CTR, BCtr.D_KY_STRT AS D_KY_STRT_BUS_CTR
, BCtr.D_KY_END AS D_KY_END_BUS_CTR, BCtr.DS_BUS_CTR
, Post.KY_POST, Post.ID_POST, Post.D_KY_STRT AS D_KY_STRT_POST, Post.D_KY_END AS D_KY_END_POST
, Post.DS_POST, Post.NM_POST_HLD, Post.ID_POST_MGR_TYPE, Post.I_FRN_TEMP
, BSeg.KY_BUS_SEG, BSeg.ID_BUS_SEG, BSeg.D_KY_STRT AS D_KY_STRT_BUS_SEG
, BSeg.D_KY_END AS D_KY_END_BUS_SEG, BSeg.DS_BUS_SEG, BSeg.I_REP_BUS_SEG
, CLG.KY_CLG, CLG.N_CLG, CLG.D_KY_STRT AS D_KY_STRT_CLG, CLG.D_KY_END AS D_KY_END_CLG
, CLG.NM_CLG, CLG.I_FRN_CLG, CLG.NM_FRN, CLG.PRR_POST
, Rgn.KY_RGN, Rgn.ID_RGN, Rgn.D_KY_STRT AS D_KY_STRT_RGN
, Rgn.D_KY_END AS D_KY_END_RGN, Rgn.DS_RGN, Rgn.NM_RGN_HLD
, PrdG5.ID_SMB_Prod_G5, PrdG5.I_LEND_DPST, PrdG5.I_REP_SMB_Prod_G5, PrdG5.DS_SMB_Prod_G5
, PrdG4.DS_SMB_Prod_G4, PrdG4.ID_SMB_Prod_G4
, PrdG3.DS_SMB_Prod_G3, PrdG3.ID_SMB_Prod_G3
, PrdG2.DS_SMB_Prod_G2, PrdG2.ID_SMB_Prod_G2
, PrdG1.DS_SMB_Prod_G1, PrdG1.ID_SMB_Prod_G1
, Prod.DS_PROD, Prod.ID_PROD_GRP, Prod.ID_PROD
, OLOB.ID_OWNG_LOB, OLOB.DS_OWNG_LOB
, PRPst.KY_POST AS PR_KY_POST, PRPst.ID_POST AS PR_ID_POST, PRPst.D_KY_STRT AS PR_D_KY_STRT_POST
, PRPst.D_KY_END AS PR_D_KY_END_POST, PRPst.DS_POST AS PR_DS_POST, PRPst.NM_POST_HLD AS PR_NM_POST_HLD
, PRPst.ID_POST_MGR_TYPE AS PR_ID_POST_MGR_TYPE, PRPst.I_FRN_TEMP AS PR_I_FRN_TEMP
, MPst.KY_POST AS MP_KY_POST, MPst.ID_POST AS MP_ID_POST, MPst.D_KY_STRT AS MP_D_KY_STRT_POST
, MPst.D_KY_END AS MP_D_KY_END_POST, MPst.DS_POST AS MP_DS_POST, MPst.NM_POST_HLD AS MP_NM_POST_HLD
, MPst.ID_POST_MGR_TYPE AS MP_ID_POST_MGR_TYPE, MPst.I_FRN_TEMP AS MP_I_FRN_TEMP
, MgrTyp.DS_POST_MGR_TYPE
FROM dbo.Dim_Account Acct
LEFT JOIN dbo.Dim_Post MPst
ON Acct.KY_POST_MAINT = MPst.KY_POST
INNER JOIN dbo.Dim_ANZSICClassn Anzsic
ON Acct.C_ASIC = Anzsic.C_ASIC
INNER JOIN dbo.Dim_PROD Prod
ON Acct.ID_PROD = Prod.ID_PROD
AND Acct.ID_PROD_GRP = Prod.ID_PROD_GRP
AND Acct.ID_CNTRY_PROD = Prod.ID_CNTRY
INNER JOIN dbo.Dim_OwningLOB OLOB
ON Prod.ID_OWNG_LOB = OLOB.ID_OWNG_LOB
INNER JOIN dbo.Dim_SMBProductG1 PrdG1
ON Prod.ID_SMB_Prod_G1 = PrdG1.ID_SMB_Prod_G1
INNER JOIN dbo.Dim_SMBProductG2 PrdG2
ON PrdG1.ID_SMB_Prod_G2 = PrdG2.ID_SMB_Prod_G2
INNER JOIN dbo.Dim_SMBProductG3 PrdG3
ON PrdG2.ID_SMB_Prod_G3 = PrdG3.ID_SMB_Prod_G3
INNER JOIN dbo.Dim_SMBProductG4 PrdG4
ON PrdG3.ID_SMB_Prod_G4 = PrdG4.ID_SMB_Prod_G4
INNER JOIN dbo.Dim_SMBProductG5 PrdG5
ON PrdG4.ID_SMB_Prod_G5 = PrdG5.ID_SMB_Prod_G5
INNER JOIN dbo.Dim_CLG CLG
ON Acct.KY_CLG = CLG.KY_CLG
LEFT JOIN dbo.Dim_Post PRPst
ON CLG.PRR_POST = PRPst.KY_POST
INNER JOIN dbo.Dim_Post Post
ON CLG.KY_POST = Post.KY_POST
LEFT JOIN dbo.Dim_PostMgrType MgrTyp
ON Post.ID_POST_MGR_TYPE = MgrTyp.ID_POST_MGR_TYPE
INNER JOIN dbo.Dim_BusinessCentre BCtr
ON Post.KY_BUS_CTR = BCtr.KY_BUS_CTR
INNER JOIN dbo.Dim_District Distr
ON BCtr.KY_DISTR = Distr.KY_DISTR
INNER JOIN dbo.Dim_Region Rgn
ON Distr.KY_RGN = Rgn.KY_RGN
INNER JOIN dbo.Dim_BusinessSegment BSeg
ON Rgn.KY_BUS_SEG = BSeg.KY_BUS_SEG
GOThanks
Phill
--------------------
Colt 45 - the original point and click interface
-
Tried tricking it maybe? Build it as a view, then build a view around it? Or how about omitting the left join from the indexed view?
Andy
-
quote:
Tried tricking it maybe? Build it as a view, then build a view around it? Or how about omitting the left join from the indexed view?Andy
http://www.sqlservercentral.com/columnists/awarren/
Andy
Thanks for your reply.
Do you mean, create an indexed view excluding the LEFT JOIN tables. Then create a normal view that uses the indexed view and the LEFT JOIN tables?
Phill
--------------------
Colt 45 - the original point and click interface
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply