Error creating view. ROW_NUMBER()OVER(PARTITION BY

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)

Error creating view. ROW_NUMBER()OVER(PARTITION BY

Author

Topic

jql
Starting Member

30 Posts

Posted - 2010-09-02 : 06:25:55

Hi, does anyone know why, when i create a view with the code
ROW_NUMBER()OVER(PARTITION BY)
i get the following error
an unhandled win32 exception occurred in sqlwb.exe[1668]
thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-02 : 06:31:28

Can we see the actual code you use to create the view?

N 56°04'39.26"
E 12°55'05.63"

jql
Starting Member

30 Posts

Posted - 2010-09-02 : 06:54:34

SELECT DISTINCT
P.PRIMARYID, P.Forename + ' - ' + P.Surname AS NAME, CONVERT(VARCHAR, P.DATE_BIRTH, 103) AS DOB, A.STARTTIME,
CONVERT(VARCHAR, A.STARTTIME, 103) AS VISIT_DATE, CASE WHEN AT.WORD IS NULL
THEN 'Did not attend - No known reason' ELSE AT.WORD END AS REASON, V.VENUENAME, ROW_NUMBER() OVER (PARTITION BY
P.PRIMARYID ORDER BY A.STARTTIME DESC) AS SEQUENCE
FROM tblvisit A INNER JOIN
tblcustomer P ON A.PATIENTID = P.PATIENTID LEFT OUTER JOIN
tblWordlist AT ON A.APPTATTEND = AT.WORDLISTID LEFT OUTER JOIN
tblWordlist W ON A.status = W.WORDLISTID LEFT OUTER JOIN
dbo.tbllocation C ON A.CLINICID = C.CLINICID LEFT OUTER JOIN
dbo.tblbuilding V ON C.VENUEID = V.SCREENID
WHERE W.WORD IS NULL AND A.STATUS IS NULL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-02 : 08:46:05

No "create view"?

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-02 : 09:00:40

I don't get the WHERE clause.

1) If a.STATUS IS NULL, the the JOIN is invalid and hence W.WORD is automatically NULL too.
2) As result of this, the LEFT JOIN for "tblWordList W"is redundant.
3) DISTINCT is reduntant because of ROW_NUMBER

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-02 : 09:07:04

Try this query

CREATE VIEW dbo.vwMyView
AS

SELECT		cus.PrimaryID,
		cus.ForeName + ' - ' + cus.SurName AS Name,
		CONVERT(CHAR(10), cus.DATE_BIRTH, 103) AS DoB,
		vis.StartTime,
		CONVERT(CHAR(10), vis.StartTime, 103) AS VisitDate,
		COALESCE(wl.Word, 'Did not attend - No known reason') AS Reason,
		bld.VenueName,
		ROW_NUMBER() OVER (PARTITION BY cus.PrimaryID ORDER BY vis.StartTime DESC) AS Sequence
FROM		tblVisit AS vis
INNER JOIN	dbo.tblCustomer AS cus ON cus.PatientID = vis.PatientID
LEFT JOIN	dbo.tblWordList AS wl ON wl.WordListID = vis.ApptAttend
LEFT JOIN	dbo.tblLocation AS loc ON loc.ClinicID = vis.ClinicID
LEFT JOIN	dbo.tblBuilding AS bld ON bld.ScreenID = loc.VenueID
WHERE		vis.[Status] IS NULL

N 56°04'39.26"
E 12°55'05.63"

jql
Starting Member

30 Posts

Posted - 2010-09-02 : 09:14:12

Thanks Peso, very much appreciated. now works
Jql

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources