I have something as the below
declare @t table (id int identity,Price decimal(6,2))
insert into @t
select 17.5 union all
select 10.34 union all
select 2.0 union all
select 34.5
Now if I write a query as below
;with cte(id, price) as
(
select id, price
from @t
union all
select cte.id, cte.price + t.price
from cte
join @t t
on cte.id < t.id
)
select *
from @t
I am getting the below error at runtime:
Types don't match between the anchor and the recursive part....
I even tried the same after typecasting(to decimal) but with same result ....
However, if I typecast to int it works... but that should not be the case (:
The fix:
;with cte(id,price) as
(
Select id, price from @t
Union all
Select cte.id, cast(cte.price + t.price as decimal(6,2))
From cte
Join @t t
On cte.id < t.id
)
Select * from @t
Explanation:
The expression cte.price + t.price
will return the type that is not necessarily decimal(6,2), can return decimal(5,2). So it cannot union those two values after that.