SQL server - OVER SQL construct not supported

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

General SQL Server Forums

New to SQL Server Programming

SQL server - OVER SQL construct not supported

Author

Topic

Sitebuilders
Starting Member

4 Posts

Posted - 2009-11-15 : 07:00:25

When I execute the following sql lines on my store procedure:

SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY ID DESC) AS rownum from YAD2) AS YAD2 WHERE (Category = 13) AND (OnStatus = 1) AND (rownum between 1 and (1 + 15) - 1)
SELECT * FROM

(SELECT *, ROW_NUMBER() OVER(ORDER BY ID DESC) AS rownum from YD) AS YD
WHERE (Category = 13) AND (OnStatus = 1) AND
(rownum between 1 and (1 + 15) - 1)

I get the following error:
"The OVER SQL construct or statement is not supported."
And finally I get no results.

My columns on YD table are:
ID, VisitorID, Category, City, DescriptionText, Price, Phone, EmailAddress, ContactPerson, DateAdded, OnStatus

The interesting issue is that if I eliminate the WHERE (Category = 13) AND it's work. But I do want to get my data where category = 13.

Any Idea ?

Sitebuilders
Starting Member

4 Posts

Posted - 2009-11-15 : 07:37:09

Someone has an answer to this issue ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-15 : 08:03:40

What is your version of SQL Server ? over() only supports in SQL 2005 / 2008

try

print @@version

KH
[spoiler]Time is always against us[/spoiler]

Sitebuilders
Starting Member

4 Posts

Posted - 2009-11-15 : 08:21:16

I use Sql Server 2005. I found that the problem is not with the over(). When I use these line:
SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY ID DESC) AS rownum from YD) AS YD
WHERE (Category = 13) AND (OnStatus = 1) AND
(rownum between 1 and (1 + 15) - 1)
The rownum return some missing records and I have no idea why. I know that Category = 13 should return 3 rows and there no rows.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-15 : 10:02:38

can you post some sample data and the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

Sitebuilders
Starting Member

4 Posts

Posted - 2009-11-15 : 10:09:12

My columns on YD table are:
ID, VisitorID, Category, City, DescriptionText, Price, Phone, EmailAddress, ContactPerson, DateAdded, OnStatus

You can put any data inside.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources