September 20, 2015 at 11:55 am
Hello All,
I am trying to do a very small numbers table to compare A1c's against. However I am running into a issue when recursion hits the number 2.27 it starts to go out of my scope that I want with the next number being 2.27999999999999. Here is the code I'm using below. I need a Decimal(2,2) or Numeric (2,2) format with a range of 01.00 to 20.00. However every time I use Numeric or Decimal as the data type I get a error "Msg 240, Level 16, State 1, Line 5
Types don't match between the anchor and the recursive part in column "Number" of recursive query "NumberSequence"."
DECLARE @Start FLOAT , @End FLOAT ---DECIMAL(2,2) Numeric (2,2)
SELECT @Start=01.00, @End=20.00
;WITH NumberSequence( Number ) AS
(
SELECT @start as Number
UNION ALL
SELECT Number + 00.01
FROM NumberSequence
WHERE Number < @end
)
--select result
SELECT * FROM NumberSequence OPTION (MaxRecursion 2000)
Any insight would be greatly appreciated. I'm a little foggy with a cold so i'm sure its something very simple I am overlooking. Thanks!
***SQL born on date Spring 2013:-)
September 20, 2015 at 12:45 pm
thomashohner (9/20/2015)
Hello All,I am trying to do a very small numbers table to compare A1c's against. However I am running into a issue when recursion hits the number 2.27 it starts to go out of my scope that I want with the next number being 2.27999999999999. Here is the code I'm using below. I need a Decimal(2,2) or Numeric (2,2) format with a range of 01.00 to 20.00. However every time I use Numeric or Decimal as the data type I get a error "Msg 240, Level 16, State 1, Line 5
Types don't match between the anchor and the recursive part in column "Number" of recursive query "NumberSequence"."
DECLARE @Start FLOAT , @End FLOAT ---DECIMAL(2,2) Numeric (2,2)SELECT @Start=01.00, @End=20.00
;WITH NumberSequence( Number ) AS
(
SELECT @start as Number
UNION ALL
SELECT Number + 00.01
FROM NumberSequence
WHERE Number < @end
)
--select result
SELECT * FROM NumberSequence OPTION (MaxRecursion 2000)
Any insight would be greatly appreciated. I'm a little foggy with a cold so i'm sure its something very simple I am overlooking. Thanks!
Hi Thomas, you are not the first one to run into this problem;-), just use NUMERIC(38,2) to avoid the error and convert to the smaller numeric as needed in the final select, Don't ever use float or real for this, wrong types for the occation!
😎
DECLARE @Start NUMERIC(38,2) , @End NUMERIC(38,2) ---DECIMAL(2,2) Numeric (2,2)
SELECT @Start=01.00, @End=20.00
;WITH NumberSequence( Number ) AS
(
SELECT @start as Number
UNION ALL
SELECT Number + 00.01
FROM NumberSequence
WHERE Number < @end
)
--select result
,MY_STUFF AS
(
SELECT
CONVERT(NUMERIC(4,2),Number,0) AS Number
FROM NumberSequence )
SELECT * FROM MY_STUFF
OPTION (MaxRecursion 2000);
September 21, 2015 at 3:41 am
You need DECIMAL(4,2), and to ensure the recursive part of the query conforms to that data type...
You can't put 1.00 into DECIMAL(2,2), when you try, this errors:
SELECT @Start=1.00, @End=20.00
DECLARE @Start DECIMAL(4,2), @End DECIMAL(4,2) ---DECIMAL(2,2) Numeric (2,2)
SELECT @Start=1.00, @End=20.00
;WITH NumberSequence( Number ) AS
(
SELECT @start as Number
UNION ALL
SELECT [highlight="#ffff11"]CAST(Number + 00.01 as DECIMAL(4,2))[/highlight]
FROM NumberSequence
WHERE Number < @end
)
--select result
SELECT * FROM NumberSequence OPTION (MaxRecursion 2000)
Edit: change wording slightly as it didn't quite fit...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);September 21, 2015 at 4:25 am
Don't use recursive CTEs to generate number sequences. You can only have a maximum of 32,767 numbers, and it's less efficient than the CROSS JOIN method. The recursive method on my server executes in about 23ms; the CROSS JOIN (below) in 0ms.
WITH N7 AS (
SELECT n FROM (VALUES (1),(2),(3),(4),(5),(6),(7)) v(n)
)
, N49 AS (
SELECT N1.n
FROM N7 N1 CROSS JOIN N7 N2
)
, N2401 AS (
SELECT N1.n
FROM N49 N1 CROSS JOIN N49 N2
)
, NumberSequence(n) AS (
SELECT ROW_NUMBER() OVER (ORDER BY n)
FROM N2401
)
SELECT 0.99 + n*0.01
FROM NumberSequence
WHERE n <= 1901
John
September 21, 2015 at 8:04 am
Thanks so much for the feedback !
All of the solutions worked however after seeing John Mitchell-245523 CROSS JOIN solution and testing it for my self I am abandoning the Recursive CTE and switching over to that method.
Thanks again gentlemen!:-D
***SQL born on date Spring 2013:-)
September 21, 2015 at 9:04 am
Absolutely the right way to go, I just wanted to clarify the problem with the code presented as getting the right data type in a recursive CTE is one of the tricky things about it, and one of the many reasons I never use them.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply