The OVER SQL construct or statement is not supported.?

Question

The OVER SQL construct or statement is not supported.?

so.eslami

SSChasing Mays

Points: 604
More actions
June 24, 2012 at 11:07 am

#276841

i use this command for giving ranking of user but i give error
select Score, (DENSE_RANK() OVER ( order by Score desc)) AS Ranking
FROM cuscore
ORDER BY Ranking

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

so.eslami (6/24/2012)
i use this command for giving ranking of user but i give error
select Score, (DENSE_RANK() OVER ( order by Score desc)) AS Ranking
FROM cuscore
ORDER BY Ranking

not really sure what your problem is....you say "give error"...but do not provide the error message.

here is some code :

CREATE TABLE [dbo].[cuscore](

[cuscode] [int] NULL,

[score] [int] NULL

) ON [PRIMARY]

GO

INSERT INTO [dbo].[cuscore]([cuscode], [score])

SELECT 1, 5 UNION ALL

SELECT 2, 6 UNION ALL

SELECT 3, 4 UNION ALL

SELECT 4, 5 UNION ALL

SELECT 5, 9 UNION ALL

SELECT 6, 3 UNION ALL

SELECT 7, 5 UNION ALL

SELECT 8, 8 UNION ALL

SELECT 9, 2

GO

SELECT Score,

( DENSE_RANK() OVER ( ORDER BY Score DESC) ) AS Ranking

FROM cuscore

ORDER BY Ranking

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

so.eslami SSChasing Mays Points: 604 More actions · Answer 2

error is

the over construct or statement is not supported

i use ssrs 2005

so.eslami SSChasing Mays Points: 604 More actions · Answer 3

someone saied me for this problem , create procedure and then call it, but i dont know how to create that

rhythmk SSCertifiable Points: 7271 More actions · Answer 4

so.eslami (6/24/2012)
i use this command for giving ranking of user but i give error
select Score, (DENSE_RANK() OVER ( order by Score desc)) AS Ranking
FROM cuscore
ORDER BY Ranking

There is no problem in this statement from the ranking perspective.

Please provide more data where exactly you or your friend is facing issue as this will work in SSRS as well without fail.

Please check instead of this there must be some other issue.

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

GSquared SSC Guru Points: 260824 More actions · Answer 5

The most likely problem is that the database is in Compatibility level 80 (or earlier). Check that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lowell SSC Guru Points: 323518 More actions · Answer 6

GSquared (6/25/2012)
The most likely problem is that the database is in Compatibility level 80 (or earlier). Check that.

that's what i thought, but when i tested it via TSQL, it worked fine, regardless of compatibiility level.

i'm thinking it has more to do with an OLEDB driver that might be being used that does not support the OVER syntax, rather than the server itself.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

maybe worth a look..??

http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/4b7fe6f4-abf9-46ce-be5c-d655f1f87fbb

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day