Over Sql construct error - 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 2005 Forums

Transact-SQL (2005)

Over Sql construct error

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-07-21 : 05:58:17

Hallo,

When I open a new query the following code below works, but as soon as I save it as a view it gives the error "The OVER sql construct or statement is not supported" when u OK the pop up box, the results are displayed. Is there anyway I can avoid this error.

Please note: I am working with SQL server 2005, Compatability has been set to 90... any other reason why this error is prompted.. Thanks

select prac_no,col_uid,audit_end,[status],stage,audit_start, sys_time
from
(
select 
row_number() over (partition by prac_no order by sys_time desc) as rownum,
*
from
(
SELECT     TOP (100) PERCENT dbo.QryColProcessing.prac_no, MAX(dbo.QryColProcessing.col_uid) AS col_uid, MAX(dbo.QryColProcessing.audit_end) 
                      AS audit_end, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage, MAX(dbo.QryColProcessing.audit_end + 1) AS audit_start, Max (gprdsql.TblColProcessing.system_time)as sys_time
FROM         gprdsql.TblColProcessing INNER JOIN
                      dbo.QryColProcessing ON gprdsql.TblColProcessing.col_uid = dbo.QryColProcessing.col_uid
GROUP BY dbo.QryColProcessing.prac_no, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage
HAVING      (gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'cancelled') OR
                      (gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'stage 8')
) as dt
) as dt2
where rownum = 1

bwfc
Starting Member

1 Post

Posted - 2009-07-22 : 05:23:29

When you say you are saving the view are you using a CREATE VIEW statement? That should work:

CREATE VIEW v_myView AS

select prac_no,col_uid,audit_end,[status],stage,audit_start, sys_time
from
(
select
row_number() over (partition by prac_no order by sys_time desc) as rownum,
*
from
(
SELECT TOP (100) PERCENT dbo.QryColProcessing.prac_no, MAX(dbo.QryColProcessing.col_uid) AS col_uid, MAX(dbo.QryColProcessing.audit_end)
AS audit_end, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage, MAX(dbo.QryColProcessing.audit_end + 1) AS audit_start, Max (gprdsql.TblColProcessing.system_time)as sys_time
FROM gprdsql.TblColProcessing INNER JOIN
dbo.QryColProcessing ON gprdsql.TblColProcessing.col_uid = dbo.QryColProcessing.col_uid
GROUP BY dbo.QryColProcessing.prac_no, gprdsql.TblColProcessing.status, gprdsql.TblColProcessing.stage
HAVING (gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'cancelled') OR
(gprdsql.TblColProcessing.status = 'completed') AND (gprdsql.TblColProcessing.stage = 'stage 8')
) as dt
) as dt2
where rownum = 1

---- NB: You then won't be able to directly modify the view (as the GUI doesn't support it) and you will have to script view as "alter to" in order to modify in the future.

Hope it works ok.

Regards
Paul