Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 compatibility level sql server 2005(90) and error
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jung1975
Aged Yak Warrior

USA
503 Posts

Posted - 11/14/2006 :  13:01:28  Show Profile  Reply with Quote
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

United Kingdom
12543 Posts

Posted - 11/14/2006 :  13:47:07  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

jung1975
Aged Yak Warrior

USA
503 Posts

Posted - 11/14/2006 :  14:58:58  Show Profile  Reply with Quote
thank you nr! It works


Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000