Better way to write this 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)

Better way to write this view

Author

Topic

steamngn
Constraint Violating Yak Guru

306 Posts

Posted - 2006-01-13 : 11:17:59

Hi Gang,
Happy new year...
Is there a better way to write this view? It returns the proper data, but is very slow...
Andy

CREATE VIEW dbo.VI_SERVICE_NEW
AS
SELECT     dbo.TimeTable.NAM, dbo.TimeTable.SVC_DAT, COALESCE (a.SHIP_CITY, '') + ' ' + COALESCE (a.SHIP_NAM, '') + ' ' + COALESCE (a.TKT_NO, '') 
                      + ' ' + COALESCE (CAST(b.SVC_NOTES AS varchar(1000)), '') AS call1, COALESCE (b.SHIP_CITY, '') + ' ' + COALESCE (b.SHIP_NAM, '') 
                      + ' ' + COALESCE (b.TKT_NO, '') + ' ' + COALESCE (CAST(b.SVC_NOTES AS varchar(1000)), '') AS call1a, COALESCE (c.SHIP_CITY, '') 
                      + ' ' + COALESCE (c.SHIP_NAM, '') + ' ' + COALESCE (c.TKT_NO, '') + ' ' + COALESCE (CAST(c.SVC_NOTES AS varchar(1000)), '') AS call2, 
                      COALESCE (d.SHIP_CITY, '') + ' ' + COALESCE (d.SHIP_NAM, '') + ' ' + COALESCE (d.TKT_NO, '') 
                      + ' ' + COALESCE (CAST(d.SVC_NOTES AS varchar(1000)), '') AS call2a, COALESCE (e.SHIP_CITY, '') + ' ' + COALESCE (e.SHIP_NAM, '') 
                      + ' ' + COALESCE (e.TKT_NO, '') + ' ' + COALESCE (CAST(e.SVC_NOTES AS varchar(1000)), '') AS call3, COALESCE (f.SHIP_CITY, '') 
                      + ' ' + COALESCE (f.SHIP_NAM, '') + ' ' + COALESCE (f.TKT_NO, '') + ' ' + COALESCE (CAST(f.SVC_NOTES AS varchar(1000)), '') AS call3a, 
                      COALESCE (g.SHIP_CITY, '') + ' ' + COALESCE (g.SHIP_NAM, '') + ' ' + COALESCE (g.TKT_NO, '') 
                      + ' ' + COALESCE (CAST(g.SVC_NOTES AS varchar(1000)), '') AS call4, COALESCE (h.SHIP_CITY, '') + ' ' + COALESCE (h.SHIP_NAM, '') 
                      + ' ' + COALESCE (h.TKT_NO, '') + ' ' + COALESCE (CAST(h.SVC_NOTES AS varchar(1000)), '') AS call4a, COALESCE (j.SHIP_CITY, '') 
                      + ' ' + COALESCE (j.SHIP_NAM, '') + ' ' + COALESCE (j.TKT_NO, '') + ' ' + COALESCE (CAST(j.SVC_NOTES AS varchar(1000)), '') AS call5, 
                      COALESCE (k.SHIP_CITY, '') + ' ' + COALESCE (k.SHIP_NAM, '') + ' ' + COALESCE (k.TKT_NO, '') + ' ' + COALESCE (CAST(k.SVC_NOTES AS varchar(1000)),
                       '') AS call5a, COALESCE (m.SHIP_CITY, '') + ' ' + COALESCE (m.SHIP_NAM, '') + ' ' + COALESCE (m.TKT_NO, '') 
                      + ' ' + COALESCE (CAST(m.SVC_NOTES AS varchar(1000)), '') AS call6, COALESCE (n.SHIP_CITY, '') + ' ' + COALESCE (n.SHIP_NAM, '') 
                      + ' ' + COALESCE (n.TKT_NO, '') + ' ' + COALESCE (CAST(n.SVC_NOTES AS varchar(1000)), '') AS call6a
FROM         dbo.TimeTable LEFT OUTER JOIN
                      dbo.PS_TKT_HDR a ON a.TKT_NO = dbo.TimeTable.Time01 LEFT OUTER JOIN
                      dbo.PS_TKT_HDR b ON b.TKT_NO = dbo.TimeTable.Time02 LEFT OUTER JOIN
                      dbo.PS_TKT_HDR c ON b.TKT_NO = dbo.TimeTable.Time04 LEFT OUTER JOIN
                      dbo.PS_TKT_HDR d ON b.TKT_NO = dbo.TimeTable.Time05 LEFT OUTER JOIN
                      dbo.PS_TKT_HDR e ON b.TKT_NO = dbo.TimeTable.Time07 LEFT OUTER JOIN
                      dbo.PS_TKT_HDR f ON b.TKT_NO = dbo.TimeTable.Time08 LEFT OUTER JOIN
                      dbo.PS_TKT_HDR g ON b.TKT_NO = dbo.TimeTable.Time10 LEFT OUTER JOIN
                      dbo.PS_TKT_HDR h ON b.TKT_NO = dbo.TimeTable.Time11 LEFT OUTER JOIN
                      dbo.PS_TKT_HDR j ON b.TKT_NO = dbo.TimeTable.Time13 LEFT OUTER JOIN
                      dbo.PS_TKT_HDR k ON b.TKT_NO = dbo.TimeTable.Time14 LEFT OUTER JOIN
                      dbo.PS_TKT_HDR m ON b.TKT_NO = dbo.TimeTable.Time16 LEFT OUTER JOIN
                      dbo.PS_TKT_HDR n ON b.TKT_NO = dbo.TimeTable.Time17

There's never enough time to type code right,
but always enough time for a hotfix...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources