compatibility level sql server 2005(90) and error

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 2000 Forums

Transact-SQL (2000)

compatibility level sql server 2005(90) and error

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-11-14 : 13:01:28

I have a SP that using CTE.. It works fine if I set the Compatibility level to SQL 2000(80),but as soon as I change the Compatibility level to sql server 2005 ( 90), I get an error:

Msg 319, Level 15, State 1, Procedure usp_Rollforward_AR, Line 36
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 102, Level 15, State 1, Procedure usp_Rollforward_AR, Line 73
Incorrect syntax near ','.

I am not quite sure what this is happening.. I thought the CTE is the new option for SQL server 2005… what I am doing wrong?

ALTER proc [dbo].[usp_Rollforward_AR] ( @month int, @year int, @unit_abbr varchar(12) )
AS

declare @prev_month int
declare @prev_year int
declare @prev_yearB int
SET @prev_month = MONTH(DATEADD(month, @month -2, DATEADD(year, @year - 1900, 0)))
Set @prev_yearB = @year -1
SET @prev_year = YEAR(DATEADD(month, @month -2, DATEADD(year, @year - 1900, 0)))

declare @bdp int
set @bdp = ( select sum (a.bdp) from (select round(sum(bad_dept_provision ),-2) as bdp , a.month
From revenue_aggregate a
join Revenue_aggregate_assertions b on a.[month] = b.[month] and a.[year] = b.[year] and a.[Unit_abbr] = b.[Unit_abbr] and a.financial_class_code = b.financial_class_code
WHERE ((a.[month] <= @month) AND (a.[year] = @year) AND (a.[unit_abbr] = @unit_abbr))
group by a.month) a )

declare @bdp12 int
set @bdp12 = ( select sum (a.bdp) from (select round(sum(bad_dept_provision ),-2) as bdp , a.month
From revenue_aggregate a
join Revenue_aggregate_assertions b on a.[month] = b.[month] and a.[year] = b.[year] and a.[Unit_abbr] = b.[Unit_abbr] and a.financial_class_code = b.financial_class_code
WHERE ((a.[month] <= 12) AND (a.[year] = @prev_yearB) AND (a.[unit_abbr] = @unit_abbr))
group by a.month) a )

with YTDrevenue
AS
( select
(select isnull(sum(payments),0) + isnull(sum(refunds),0) + isnull(sum(bad_debt_writeoffs),0) + ( isnull( sum(system_net_revenue),0) - isnull(sum(bad_debt_writeoffs),0) )
from revenue_aggregate a
join Revenue_aggregate_assertions b on a.[month] = b.[month] and a.[year] = b.[year] and a.[Unit_abbr] = b.[Unit_abbr] and a.financial_class_code = b.financial_class_code
where a.unit_abbr = @unit_abbr and a.month <= 12 and a.year = @prev_yearB) as YTDopenBal,
a.unit_abbr, sum(payments) as payments
, sum(refunds) as refunds
,sum(bad_debt_writeoffs) as bdw
, ( sum(system_net_revenue) - sum(bad_debt_writeoffs) ) as YTDSNRbeforeBDW
,isnull(sum(supp_contractual_allowances),0) as YTDsca

,(select isnull(sum(supp_contractual_allowances),0) as [sca] from revenue_aggregate_assertions where (month <=12 and year = @prev_yearB and unit_abbr = @unit_abbr) ) as YTDscaBal

, isnull(sum(supp_contractual_allowances),0) + (select isnull(sum(supp_contractual_allowances),0) from revenue_aggregate_assertions where (month <=12 and year = @prev_yearB and unit_abbr = @unit_abbr) ) as YTDscaEnd
,
( (select isnull(sum(bad_debt_writeoffs),0) from revenue_aggregate
where a.unit_abbr = @unit_abbr
and year = @prev_yearB and month <= 12)
+ isnull(@bdp12,0) )
as YTDbdpBal

,isnull(@bdp,0) as YTDBDP

,( (select isnull(sum(bad_debt_writeoffs),0) from revenue_aggregate
where a.unit_abbr = @unit_abbr
and year = @prev_yearB and month <= 12)
+ isnull(@bdp12,0)
+ isnull(sum(bad_debt_writeoffs),0) + isnull(@bdp ,0) ) as YTDbdpEnd

from revenue_aggregate a
join Revenue_aggregate_assertions b on a.[month] = b.[month] and a.[year] = b.[year] and a.[Unit_abbr] = b.[Unit_abbr] and a.financial_class_code = b.financial_class_code
WHERE ((a.[month] <= @month) AND (a.[year] = @year) AND (a.[unit_abbr] = @unit_abbr))
group by a.unit_abbr
)

,
MTDrevenue
AS
(
select

(select isnull(sum(payments),0) + isnull(sum(refunds),0) + isnull(sum(bad_debt_writeoffs),0) + ( isnull( sum(system_net_revenue),0) - isnull(sum(bad_debt_writeoffs),0) )
from revenue_aggregate a
join Revenue_aggregate_assertions b on a.[month] = b.[month] and a.[year] = b.[year] and a.[Unit_abbr] = b.[Unit_abbr] and a.financial_class_code = b.financial_class_code

where a.unit_abbr = @unit_abbr and (a.year * 100) + a.month <= (@prev_year * 100) + @prev_month) as MTDopenBal,

a.unit_abbr ,isnull(sum(payments),0)as payments,isnull(sum(refunds),0) as refunds, isnull(sum(bad_debt_writeoffs),0) as bdw,
isnull( sum(system_net_revenue),0) - isnull(sum(bad_debt_writeoffs),0) as MTDSNRbeforeBDW

,isnull(sum(supp_contractual_allowances),0) as MTDsca
,(select isnull(sum(supp_contractual_allowances),0) from revenue_aggregate_assertions where unit_abbr = @unit_abbr and (year * 100) + month <= (@prev_year * 100) + @prev_month) as MTDscaBal
,(isnull(sum(supp_contractual_allowances),0) + (select isnull(sum(supp_contractual_allowances),0) from revenue_aggregate_assertions where unit_abbr = @unit_abbr and (year * 100) + month <= (@prev_year * 100) + @prev_month)) as MTDscaEnd

----BDP
,isnull(round(sum(Bad_Dept_Provision),-2),0) as MTDBDP

, ( (select isnull(round(sum(bad_debt_writeoffs),-2),0) from revenue_aggregate
where unit_abbr = @unit_abbr
and (year * 100) + month <= (@prev_year * 100) + @prev_month )
+
(select isnull(round(sum(bad_dept_provision),-2),0) from revenue_aggregate_assertions
where unit_abbr = @unit_abbr
and (year * 100) + month <= (@prev_year * 100) + @prev_month ))as MTDbdpBal
,

( (select isnull(round(sum(bad_debt_writeoffs),-2),0) from revenue_aggregate
where unit_abbr = @unit_abbr
and (year * 100) + month <= (@prev_year * 100) + @prev_month )
+
(select isnull(round(sum(bad_dept_provision),-2),0) from revenue_aggregate_assertions
where unit_abbr = @unit_abbr
and (year * 100) + month <= (@prev_year * 100) + @prev_month )+
isnull(sum(bad_debt_writeoffs),0) + isnull(round(sum(bad_dept_provision),-2),0) ) as MTDbdpEnd

from revenue_aggregate a
join Revenue_aggregate_assertions b on a.[month] = b.[month] and a.[year] = b.[year] and a.[Unit_abbr] = b.[Unit_abbr] and a.financial_class_code = b.financial_class_code
WHERE ((a.[month] = @month) AND (a.[year] = @year) AND (a.[unit_abbr] = @unit_abbr))
group by a.unit_abbr
)

select isnull( (MTDrevenue.MTDopenBal),0) as MTDopenBal,isnull((YTDrevenue.YTDopenBal),0) as YTDopenBal, isnull((YTDrevenue.payments),0) as YTDpayment, isnull((MTDrevenue.payments),0) as MTDPayment,isnull((YTDrevenue.refunds),0) as YTDrefunds,
isnull((MTDrevenue.refunds),0) as MTDrefunds, isnull(( YTDrevenue.bdw),0) as YTDbdw , isnull((MTDrevenue.bdw),0) as MTDbdw, isnull((YTDrevenue.YTDSNRbeforeBDW),0) as YTDSNRbeforeBDW,isnull((MTDrevenue.MTDSNRbeforeBDW),0) as MTDSNRbeforeBDW,
( isnull(( MTDrevenue.MTDopenBal),0) + isnull((MTDrevenue.payments),0) + isnull((MTDrevenue.refunds),0)+ isnull((MTDrevenue.bdw),0) + isnull((MTDrevenue.MTDSNRbeforeBDW),0 ) ) as MTDEndBal,

( isnull(( YTDrevenue.YTDopenBal),0) + isnull((YTDrevenue.payments),0) + isnull((YTDrevenue.refunds),0)+ isnull((YTDrevenue.bdw),0) + isnull((YTDrevenue.YTDSNRbeforeBDW),0 ) ) as YTDEndBal,
isnull((YTDrevenue.YTDsca) ,0 ) as YTDsca,
isnull((MTDrevenue.MTDsca) , 0) as MTDsca,
isnull( (YTDrevenue.YTDBDP) ,0 ) as YTDBDP,
isnull( (YTDrevenue.bdw) ,0 ) as YTDbdw,
isnull( (MTDrevenue.bdw) ,0 ) as MTDbdw,
isnull((MTDrevenue.MTDBDP) , 0) as MTDBDP,
(YTDrevenue.YTDscaBal) as YTDscaBal ,
(MTDrevenue.MTDscaBal) as MTDscaBal ,
isnull((YTDrevenue.YTDbdpBal),0) as YTDbdpBal ,
isnull((MTDrevenue.MTDbdpBal),0) as MTDbdpBal ,
isnull((YTDrevenue.YTDbdpEnd),0) as YTDbdpEnd ,
isnull((MTDrevenue.MTDbdpEnd),0) as MTDbdpEnd,
(MTDrevenue.MTDscaEnd) as MTDscaEnd,
(YTDrevenue.YTDscaEnd) as YTDscaEnd

from MTDrevenue join YTDrevenue on YTDRevenue.unit_abbr = MTDrevenue.unit_abbr

nr
SQLTeam MVY

12543 Posts

Posted - 2006-11-14 : 13:47:07

Try putting a ; before the "with".

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-11-14 : 14:58:58

thank you nr! It works

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources