Arithmetic overflow error converting numeric to data type numeric

• water490

  Ten Centuries

  Points: 1048

  More actions

  May 2, 2023 at 9:44 pm

  Go to Answer

  #4183172

  Hi everyone

  I am not able to fix this error.  I tried casting as numeric but no luck.  What do you suggest?

  table

  CREATE TABLE [dbo].[Table1](
  [UNDERLYING_SYMBOL] [nchar](10) NOT NULL,
  [QUOTE_DATE] [date] NOT NULL,
  [Ratio_V] [numeric](7, 6) NOT NULL,
  [Ratio_O] [numeric](7, 6) NOT NULL,
  [Ratio_D] [numeric](7, 6) NOT NULL
  ) ON [PRIMARY]
  GO

  query

  TRUNCATE TABLE DBO.Table1
  
  INSERT INTO DBO.Table1
  SELECTT3.UNDERLYING_SYMBOL,
  T3.QUOTE_DATE,
  IIF(T4.C_V = 0, 0, 1.0 * T3.P_V / T4.C_V) AS Ratio_V,
  IIF(T4.C_O = 0, 0, 1.0 * T3.P_O / T4.C_O) AS Ratio_O,
  IIF(T4.C_D = 0, 0, 1.0 * T3.P_D / T4.C_D) AS Ratio_D
  FROM ...

  error

  Arithmetic overflow error converting numeric to data type numeric.
  The statement has been terminated.

• ratbak

  Default port

  Points: 1407

  More actions

  May 2, 2023 at 11:56 pm

  Answer

  #4183203

  What are the data types (w/ precision & scale) of T3.P_V, T4.C_V, T3.P_O, T4.C_O, T3.P_D, & T4.C_D?

  Can you provide a sample script of failing data?

   

• ratbak

  Default port

  Points: 1407

  More actions

  May 2, 2023 at 11:56 pm

  #4183204

  Post duplicated

• water490

  Ten Centuries

  Points: 1048

  More actions

  May 3, 2023 at 12:05 am

  #4183206

  thank you for that!

  The source table had a different level of precision.  Once I matched the destination so it matched the source it worked.

• ratbak

  Default port

  Points: 1407

  More actions

  May 3, 2023 at 12:07 am

  #4183209

  You probably have one or more calculations w/ a result > 100

  Contrived example...  This succeeds:

  This succeeds:
  SELECT 'Symbol',
  '2023-05-02',
  CAST(IIF(0 = -1, 0, 1.0 * 1.234567 / .01234567) AS NUMERIC(7,4)) AS Ratio_V,
  CAST(IIF(0 = -1, 0, 1.0 * 1.234567 / .01234567) AS NUMERIC(7,4)) AS Ratio_O,
  CAST(IIF(0 = -1, 0, 1.0 * 1.234567 / .01234567) AS NUMERIC(7,4)) AS Ratio_D;

  This fails:

  SELECT 'Symbol',
  '2023-05-02',
  CAST(IIF(0 = -1, 0, 1.0 * 1.234567 / .01234567) AS NUMERIC(7,5)) AS Ratio_V,
  CAST(IIF(0 = -1, 0, 1.0 * 1.234567 / .01234567) AS NUMERIC(7,5)) AS Ratio_O,
  CAST(IIF(0 = -1, 0, 1.0 * 1.234567 / .01234567) AS NUMERIC(7,5)) AS Ratio_D;