Need substitute for using Cross Apply with SQL Server 2000
I wrote the following script that works fine in my SQL Server 2008 developer database using the CROSS APPLY parameter but it will not work for the client who has a SQL Server 2000 database and does not want to change the compatibility setting in SSMS.
Can someone suggest a way to convert my script below so as not to use the CROSS APPLY parameter? Thanks!
DECLARE @Flag INT SET @Flag = 1 WHILE (@Flag < (SELECT TOP 1 (COUNT(CUSTOMERPN)) AS COUNTCUST FROM FC_Nestle GROUP BY CustomerPN ORDER BY COUNTCUST DESC)) BEGIN --UPDATE AFS_TOPRODUCE COUNTS UPDATE FC_Nestle SET AFS_ToProduce = CustReqQty - AFS_OH --UPDATE SUBSEQUENT AFS_OH INVENTORY COUNTS update FC_Nestle set AFS_OH = - fc2.AFS_ToProduce from FC_Nestle CROSS APPLY ( select fc2.AFS_ToProduce from ( select top 1 fc2.AFS_ToProduce from FC_Nestle fc2 where fc2.ForecastID < FC_Nestle.ForecastID and fc2.CustomerPN = FC_Nestle.CustomerPN order by fc2.ForecastID desc ) fc2 where fc2.AFS_ToProduce < 0 ) fc2 where FC_Nestle.AFS_ToProduce > 0 SET @Flag = @Flag + 1 END
One Solution collect form web for “Need substitute for using Cross Apply with SQL Server 2000”
this should help you out:
update fc_test set AFS_OH = - fc2.AFS_ToProduce from fc_test -- Self join to find previous row inner join fc_test fc2 -- which has lower forecastid on fc2.ForecastID < fc_test.ForecastID where fc_test.AFS_ToProduce > 0 -- and negative afs_toproduce and fc2.afs_toproduce < 0 -- and is a row immediately before -- ie there is no other row closer to fc_test then current fc2 and not exists (select null from fc_test fc3 where fc3.forecastid > fc2.forecastid and fc3.ForecastID < fc_test.ForecastID)
I’m using the same table and column names as last time.
Live test is at Sql Fiddle.