CODEWITHSUNDEEP
sqlsql-serverrecursioncommon-table-expressionsql-server-2016
user2181700
user2181700

Reputation: 177

cte recursion error don't match between anchor

This might be my fault in understanding the data types but ... I get this error

Msg 240, Level 16, State 1, Line 7 Types don't match between the anchor and the recursive part in column "Number" of recursive query "Series".

with this code 

WITH Series AS 
( 
  SELECT CAST(-5 AS Decimal(5, 2)) AS Number 
  UNION ALL 
  SELECT CAST(-5 AS Decimal(5, 2)) + 0.5   AS Number
  FROM Series 
  WHERE Number  <= CAST(5 AS Decimal(5, 2))
) 
SELECT  * 
Number, 
ROW_NUMBER() OVER(ORDER BY Number ASC) AS id
FROM Series 
OPTION (MAXRECURSION 21);

my goal is to have a series of numbers from -5 to 5 with a interval of 0.5

Upvotes: 0

Views: 410

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

I'll often use a Table-Valued function to create dynamic ranges. Easy to use a a standalone or incorporate within a CROSS APPLY or any other subquery.

Example

Select * from [dbo].[tvf-Range-Number](-5,5,.5)

Returns

RetSeq  RetVal
1       -5.00
2       -4.50
3       -4.00
4       -3.50
....
17       3.00
18       3.50
19       4.00
20       4.50
21       5.00

The UDF if Interested

CREATE FUNCTION [dbo].[tvf-Range-Number] (@R1 money,@R2 money,@Incr money)
Returns Table
Return (
    with cte0(M) As (Select cast((@R2-@R1)/@Incr as int)),
         cte1(N) As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
         cte2(N) As (Select Top (Select M from cte0) Row_Number() over (Order By (Select NULL)) From cte1 a,cte1 b,cte1 c,cte1 d,cte1 e,cte1 f,cte1 g,cte1 h )

    Select RetSeq=1,RetVal=@R1 Union All Select N+1,(N*@Incr)+@R1
    From   cte2   
)
-- Max 100 million observations 
-- Select * from [dbo].[tvf-Range-Number](0,4,0.25)

Upvotes: 0

Radim Bača
Radim Bača

Reputation: 10701

You have just few mistakes

WITH Series AS 
( 
  SELECT CAST(-5 AS Decimal(5, 2)) AS Number 
  UNION ALL 
  SELECT CAST((number + 0.5) AS Decimal(5, 2))    AS Number
  FROM Series s
  WHERE s.Number  <= 4.5
) 
SELECT  Number, 
      ROW_NUMBER() OVER(ORDER BY Number ASC) AS id
FROM Series

dbfiddle demo

Upvotes: 1

Related Questions