Help with small recursive numbers table

• thomashohner

  SSCrazy Eights

  Points: 9856

  More actions

  September 20, 2015 at 11:55 am

  #300764

  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:-)

• Eirikur Eiriksson

  SSC Guru

  Points: 182947

  More actions

  September 20, 2015 at 12:45 pm

  #1827965

  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);

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  September 21, 2015 at 3:41 am

  #1828021

  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);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]