Not a recognized function Row_number()

[Jcfx]

I have a Report SErvices report that is giving me fits. The main reason is duplicate data that occurs for certain records. Someone suggested I use Row_number() in the following manner for the fields that like to duplicate.

Select
vo.ord_bookedby AS BookedBy
,vo.ord_startdate 
,vo.Office
,vo.ord_totalmiles as TotRevMiles
,TotalCharges=
	CASE ROW_NUMBER() OVER(Partition BY vo.ord_hdrnumber ORDER BY vo.ord_hdrnumber)
	WHEN 1 THEN vo.ord_totalcharge ELSE NULL End
,vo.ord_shipper AS Shipper
,vo.ord_consignee as Consignee
,vo.ord_rate
,Linehaul = 
	CASE ROW_NUMBER() OVER(partition BY vo.ord_hdrnumber ORDER BY vo.ord_hdrnumber)
	WHEN 1 Then vo.ord_linehaul ELSE NULL END 
,Accessorial = 
	CASE ROW_NUMBER() OVER(partition BY vo.ord_hdrnumber ORDER BY vo.ord_hdrnumber)
	WHEN 1 THEN vo.ord_accessorial_chrg ELSE NULL END 
,vo.ord_revenue_pay as RevenuePay
,CarrierPay = 
	CASE ROW_NUMBER() OVER(partition BY vcd.ord_hdrnumber ORDER BY vcd.ord_hdrnumber)
	WHEN 1 THEN vcd.CarrierPay ELSE NULL End
,DriverPay = 
	CASE ROW_NUMBER() OVER(partition BY vcd.ord_hdrnumber ORDER BY vcd.ord_hdrnumber)
	WHEN 1 THEN vcd.DriverPay ELSE NULL END  
from
v_rpt_orders vo
LEFT OUTER join
v_rpt_legheader vl
ON vo.ord_hdrnumber = vl.ord_hdrnumber
LEFT OUTER join
v_rpt_stops vs
ON vl.lgh_number = vs.lgh_number
LEFT OUTER join
v_Rpt_CarrDrivePaydetails vcd
ON vcd.lgh_number = vl.lgh_number

I receive the following error:
Msg 195, Level 15, State 10, Procedure rpt_OrderOverview, Line 38
'ROW_NUMBER' is not a recognized function name.

Sample data:

StopOrder	stp_number	lgh_number	BookedBy	ord_startdate	Office	TotRevMiles	TotalCharges	Shipper	Consignee	ord_rate	Linehaul	Accessorial	RevenuePay	CarrierPay	DriverPay
767104	837788	240200	sa	10/1/06 7:53 PM	GRREW	525	1279.18	CLWN	REDGE	995	995	284.18	995	0	244.4
767104	837789	240200	sa	10/1/06 7:53 PM	GRREW	525	1279.18	CLWN	REDGE	995	995	284.18	995	0	244.4
767104	838972	240200	sa	10/1/06 7:53 PM	GRREW	525	1279.18	CLWN	REDGE	995	995	284.18	995	0	244.4
767104	838973	240200	sa	10/1/06 7:53 PM	GRREW	525	1279.18	CLWN	REDGE	995	995	284.18	995	0	244.4
767104	840203	240200	sa	10/1/06 7:53 PM	GRREW	525	1279.18	CLWN	REDGE	995	995	284.18	995	0	244.4
767104	840457	240200	sa	10/1/06 7:53 PM	GRREW	525	1279.18	CLWN	REDGE	995	995	284.18	995	0	244.4
767268	838796	240444	JSMOE	10/1/06 8:00 AM	GRREW	1870	6042.3	KOHST	MLFGE	5500	5500	542.3	5500	6000	0
767268	838797	240444	JSMOE	10/1/06 8:00 AM	GRREW	1870	6042.3	KOHST	MLFGE	5500	5500	542.3	5500	6000	0
767278	838948	240487	TTYRE	10/1/06 8:55 AM	GRREW	93	560.88	KOHST	TOOTP	550	550	10.88	550	0	527.28
767278	838949	240487	TTYRE	10/1/06 8:55 AM	GRREW	93	560.88	SKJVO	TOOTP	550	550	10.88	550	0	527.28
767278	838950	240487	TTYRE	10/1/06 8:55 AM	GRREW	93	560.88	SKJVO	TOOTP	550	550	10.88	550	0	527.28
767279	838954	240488	BILLET	10/1/06 11:00 AM	GRREW	472	884.78	DOOMD	TOOTP	746.16	746.16	138.62	701.16	0	629.43
767279	838955	240488	BILLET	10/1/06 11:00 AM	GRREW	472	884.78	DOOMD	DLGHT	746.16	746.16	138.62	701.16	0	629.43
767279	838956	240488	BILLET	10/1/06 11:00 AM	GRREW	472	884.78	DOOMD	DLGHT	746.16	746.16	138.62	701.16	0	629.4

I cannot do an aggregate of an aggregate in Reporting Services like I would in crystal to ignore the duplicates so I am trying to do this at the sql level.



Julie
CRXI CE10 / RS2005 Sql DB

 

[bborissov]

ROW_NUMBER is SQL 2005 function, you can't use it with SQL 2000 or smaller databases. Check Compatibility level of your DataBase.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Jcfx]

Thank you that was it..

Do you know of anyway I can do something similar without that function?

Julie
CRXI CE10 / RS2005 Sql DB

 

[SQLSister]

I usually throw the data into a temp table or table variable with an identitiy field and then use that.


Questions about posting. See faq183-874
Click here to help with Hurricane Relief