Re: Error when creating a new view

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)

Re: Error when creating a new view

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-07-16 : 11:07:33

Hallo,

I have the following select query which works fine.. However, when I try to create a view and copy this query I receive the following error;

SQL syntax Errors Encountered

The following errors were encountered while parsing the contents of the SQL pane:

The OVER SQL construct or statement is not supported.

Please find the code below- Thank you

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-16 : 11:24:46

So are you trying to create this view in SQL Server 2000?
ROW_NUMBER() is working in SQL Server 2005 and higher.

No, you're never too old to Yak'n'Roll if you're too young to die.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-07-16 : 11:30:51

No, I am trying to create this view in SQL Server 2005,
and am working with Microsoft SQL server 2005, but still get the problem!!!

Help Please!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-16 : 11:51:01

Which way are you going to create the view?
Any form of a DESIGNER or just in SSMS in query window making a simple:

create view your_viewname as
select ...

No, you're never too old to Yak'n'Roll if you're too young to die.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-07-16 : 11:55:30

Thanks for the reply!!

I used

create view your_viewname as
select ...

and created the view ok. But whenever I right click and select Design of the view the error is given. When I say OK the view opens...

I just want to get rid of the error..

Any suggestions .. thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-16 : 11:58:59

The syntax is correct but not all features can be supported by the graphical Query Designer.
That's a known problem...

No, you're never too old to Yak'n'Roll if you're too young to die.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-07-16 : 12:01:05

So I need this select query so badly for my application...

Is there any other way I can re-write this select query with similar results..

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-16 : 12:06:27

Now I don't know where is the problem.
You wrote that you created the view ok.
Only the designer is giving error.
What has the designer to do with using the view in an application?

No, you're never too old to Yak'n'Roll if you're too young to die.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2009-07-16 : 12:13:09

I have saved the view, right!!

I can keep it in the view list and it works.. in couple of months if myself or any member of the team want to open it!! It will bring up the error. Therefore, I need to get rid of the error or re-create the query to eliminate the problem with similar reults..

Please assist.. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources