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

Transact-SQL (2000)

CTE error

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-04-19 : 12:44:17

I have a below SP that is using CTE,but I am keep getting an error:

With AgingGT
ASIncorrect syntax near the keyword 'With'.

create proc [dbo].[usp_CheckOOB] ( @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 )

With 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

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
)

With AgingGT
AS
(select
sum(convert(numeric(10,0),[grand_total])) as GT , unit_abbr
from aging_aggregate a
where a.[month] = @month and a.[year] =@year and a.unit_abbr = @unit_abbr
)

select
AgingGT.GT - ( isnull(( MTDrevenue.MTDopenBal),0) + isnull((MTDrevenue.payments),0) + isnull((MTDrevenue.refunds),0)+ isnull((MTDrevenue.bdw),0) + isnull((MTDrevenue.MTDSNRbeforeBDW),0 ) )
from AgingGT join MTDrevenue on a.unit_abbr = MTDrevenue.unit_abbr

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-19 : 13:25:09

What is the database compatibility level of your user database?

Tara Kizer
aka tduggan

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-04-19 : 14:48:57

It's 80

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-19 : 14:52:16

80 means SQL Server 2000. You must change it to 90 in order to use 2005 features.

The database compatibility level remains at the original level during an upgrade. SQL Server does not change it for you.

Tara Kizer
aka tduggan

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-04-19 : 15:06:21

ok. How can I change the database compatibility level?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-04-19 : 15:22:40

In 2000, you'd use sp_dbcmptlevel. Check the documentation for 2005 to see if that's still the case for 2005.

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources