Row numbers - 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)

Row numbers

Author

Topic

Jouni79
Starting Member

9 Posts

Posted - 2009-02-02 : 02:22:02

Hello everybody,

Below there is my query.I dont know what i am doing wrong.I try to get row numbers into resultset. I get this error message: The OVER SQL construct or statement is not supported.

I am using OrderReportData view. Here Is query:

SELECT ROW_NUMBER() OVER (OrderLine BY OrderLineID) AS ROWID, dbo.OrderHeader.OrderHeaderID, dbo.OrderHeader.OrderDate,
dbo.Supplier.Name AS SupplierName, dbo.Supplier.Address1 AS SupplierAddress1, dbo.Supplier.Address2 AS SupplierAddress2, dbo.Supplier.Address3 AS SupplierAddress3, dbo.Supplier.ZipCode AS SupplierZipCode, dbo.Supplier.City AS SupplierCity, dbo.Supplier.Country AS SupplierCountry, dbo.OrderHeader.Reference AS OrderReference,dbo.OrderHeader.ReferenceNumber AS OrderReferenceNumber, dbo.OrderLine. Product, dbo.OrderLine.Quantity, dbo.OrderLine.Unit, dbo.OrderLine.Info, dbo.Ship.Name AS ShipName, dbo.State.Name AS OrderState, dbo.OrderLine.Price, dbo.OrderHeader.Info AS OrderInfo,dbo.OrderHeader.DeliveryAddress, dbo.OrderHeader.ModifiedBy, dbo.OrderLine.OrderLineID
FROM
dbo.OrderHeader INNER JOIN dbo.OrderLine ON dbo.OrderHeader.OrderHeaderID = dbo.OrderLine.OrderHeaderID INNER JOIN
dbo.Ship ON dbo.OrderHeader.ShipID = dbo.Ship.ShipID INNER JOIN
dbo.State ON dbo.OrderHeader.StateID = dbo.State.StateID INNER JOIN
dbo.Supplier ON dbo.OrderHeader.SupplierID = dbo.Supplier.SupplierID
WHERE (dbo.OrderHeader.OrderHeaderID > 1)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-02 : 02:26:14

1. You have to use SQL Server 2005 or later.
2. Compatibility level must be set to 90 or higher.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 02:28:36

what does below return?
SELECT @@VERSIOn
EXEC db_cmptlevel 'yourdb'

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-02-02 : 02:30:29

quote:
Originally posted by Jouni79

Hello everybody,

Below there is my query.I dont know what i am doing wrong.I try to get row numbers into resultset. I get this error message: The OVER SQL construct or statement is not supported.

I am using OrderReportData view. Here Is query:

SELECT ROW_NUMBER() OVER (Order~~Line~~ BY OrderLineID) AS ROWID, dbo.OrderHeader.OrderHeaderID, dbo.OrderHeader.OrderDate,
dbo.Supplier.Name AS SupplierName, dbo.Supplier.Address1 AS SupplierAddress1, dbo.Supplier.Address2 AS SupplierAddress2, dbo.Supplier.Address3 AS SupplierAddress3, dbo.Supplier.ZipCode AS SupplierZipCode, dbo.Supplier.City AS SupplierCity, dbo.Supplier.Country AS SupplierCountry, dbo.OrderHeader.Reference AS OrderReference,dbo.OrderHeader.ReferenceNumber AS OrderReferenceNumber, dbo.OrderLine. Product, dbo.OrderLine.Quantity, dbo.OrderLine.Unit, dbo.OrderLine.Info, dbo.Ship.Name AS ShipName, dbo.State.Name AS OrderState, dbo.OrderLine.Price, dbo.OrderHeader.Info AS OrderInfo,dbo.OrderHeader.DeliveryAddress, dbo.OrderHeader.ModifiedBy, dbo.OrderLine.OrderLineID
FROM
dbo.OrderHeader INNER JOIN dbo.OrderLine ON dbo.OrderHeader.OrderHeaderID = dbo.OrderLine.OrderHeaderID INNER JOIN
dbo.Ship ON dbo.OrderHeader.ShipID = dbo.Ship.ShipID INNER JOIN
dbo.State ON dbo.OrderHeader.StateID = dbo.State.StateID INNER JOIN
dbo.Supplier ON dbo.OrderHeader.SupplierID = dbo.Supplier.SupplierID
WHERE (dbo.OrderHeader.OrderHeaderID > 1)

Rahul Shinde

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-02 : 02:37:01

wow! that was a nice catch

Jouni79
Starting Member

9 Posts

Posted - 2009-02-02 : 04:21:46

Now the statement is ROW_NUMBER() OVER (Order BY OrderLineID) AS ROWID. I still get message:he OVER SQL construct or statement is not supported.

quote:
Originally posted by ra.shinde

quote:
Originally posted by Jouni79

Hello everybody,

Below there is my query.I dont know what i am doing wrong.I try to get row numbers into resultset. I get this error message: The OVER SQL construct or statement is not supported.

I am using OrderReportData view. Here Is query:

SELECT ROW_NUMBER() OVER (Order~~Line~~ BY OrderLineID) AS ROWID, dbo.OrderHeader.OrderHeaderID, dbo.OrderHeader.OrderDate,
dbo.Supplier.Name AS SupplierName, dbo.Supplier.Address1 AS SupplierAddress1, dbo.Supplier.Address2 AS SupplierAddress2, dbo.Supplier.Address3 AS SupplierAddress3, dbo.Supplier.ZipCode AS SupplierZipCode, dbo.Supplier.City AS SupplierCity, dbo.Supplier.Country AS SupplierCountry, dbo.OrderHeader.Reference AS OrderReference,dbo.OrderHeader.ReferenceNumber AS OrderReferenceNumber, dbo.OrderLine. Product, dbo.OrderLine.Quantity, dbo.OrderLine.Unit, dbo.OrderLine.Info, dbo.Ship.Name AS ShipName, dbo.State.Name AS OrderState, dbo.OrderLine.Price, dbo.OrderHeader.Info AS OrderInfo,dbo.OrderHeader.DeliveryAddress, dbo.OrderHeader.ModifiedBy, dbo.OrderLine.OrderLineID
FROM
dbo.OrderHeader INNER JOIN dbo.OrderLine ON dbo.OrderHeader.OrderHeaderID = dbo.OrderLine.OrderHeaderID INNER JOIN
dbo.Ship ON dbo.OrderHeader.ShipID = dbo.Ship.ShipID INNER JOIN
dbo.State ON dbo.OrderHeader.StateID = dbo.State.StateID INNER JOIN
dbo.Supplier ON dbo.OrderHeader.SupplierID = dbo.Supplier.SupplierID
WHERE (dbo.OrderHeader.OrderHeaderID > 1)

Rahul Shinde

Jouni79
Starting Member

9 Posts

Posted - 2009-02-02 : 04:23:47

Version is:
Microsoft SQL Server 2005 - 9.00.3073.00 (Intel X86) Aug 5 2008 12:31:12 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
cmptlevel give me error message:
Could not find stored procedure 'db_cmptlevel'.