Msg 8115 Arithmetic overflow error when I am trying to convert a balance amount

Question

I need some help. I have created a view in sql 2012 that will take a persons id# and display an entry date and the balance that was entered on that date. I've tried to convert the balance and even tried increasing the entry date and balance amount fields from 10.

The balance_amount in my file has no more than 5 digits before the decimal and 6 digits after the decimal.

I need the output to be:

people_org_code_id | entry_date1 | balance1 | entry_date2 | balance2
--------------------------------------------------------------------
001234567            01/01/2018     2455.33   02/26/2018     358.33
005212222            01/24/2018     25.00

and so forth until it reaches both entry_date15 and balance15.

The code I have so far is:

    SELECT people_org_code_id, 
   [entry_date1]      AS entry_date1, 
   [balance_amount1]  AS balance1, 
   [entry_date2]      AS entry_date2, 
   [balance_amount2]  AS balance2, 
   [entry_date3]      AS entry_date3, 
   [balance_amount3]  AS balance3, 
   [entry_date4]      AS entry_date4, 
   [balance_amount4]  AS balance4, 
   [entry_date5]      AS entry_date5, 
   [balance_amount5]  AS balance5, 
   [entry_date6]      AS entry_date6, 
   [balance_amount6]  AS balance6, 
   [entry_date7]      AS entry_date7, 
   [balance_amount7]  AS balance7, 
   [entry_date8]      AS entry_date8, 
   [balance_amount8]  AS balance8, 
   [entry_date9]      AS entry_date9, 
   [balance_amount9]  AS balance9, 
   [entry_date10]     AS entry_date10, 
   [balance_amount10] AS balance_amount10, 
   [entry_date11]     AS entry_date11, 
   [balance_amount11] AS balance_amount11, 
   [entry_date12]     AS entry_date12, 
   [balance_amount12] AS balance_amount12, 
   [entry_date13]     AS entry_date13, 
   [balance_amount13] AS balance_amount13, 
   [entry_date14]     AS entry_date14, 
   [balance_amount14] AS balance_amount14, 
   [entry_date15]     AS entry_date15, 
   [balance_amount15] AS balance_amount15 
   FROM  (SELECT people_org_code_id, 
          col + '_' + CONVERT(NVARCHAR(2), rw, 1) AS col, 
          val 
   FROM   (SELECT cc.people_org_code_id, 
                  CONVERT(NVARCHAR(13), Cast(cc.entry_date AS DATE), 1) 
                  AS 
                  entry_date, 
                  CONVERT(NVARCHAR(13), Cast( 
                  cc.balance_amount AS decimal(5,2))) AS balance_amt, 
                  Row_number() 
                    OVER( 
                      partition BY cc.people_org_code_id 
                      ORDER BY cc.entry_date) 
                  AS rw 
           FROM   chargecredit cc) AS q1 
  UNPIVOT (val 
  FOR col IN (entry_date, balance_amt)) AS u1) x 
  PIVOT (Max(val) 
  FOR col IN ([entry_date1], 
                    [balance_amount1], 
                    [entry_date2], 
                    [balance_amount2], 
                    [entry_date3], 
                    [balance_amount3], 
                    [entry_date4], 
                    [balance_amount4], 
                    [entry_date5], 
                    [balance_amount5], 
                    [entry_date6], 
                    [balance_amount6], 
                    [entry_date7], 
                    [balance_amount7], 
                    [entry_date8], 
                    [balance_amount8], 
                    [entry_date9], 
                    [balance_amount9], 
                    [entry_date10], 
                    [balance_amount10], 
                    [entry_date11], 
                    [balance_amount11], 
                    [entry_date12], 
                    [balance_amount12], 
                    [entry_date13], 
                    [balance_amount13], 
                    [entry_date14], 
                    [balance_amount14], 
                    [entry_date15], 
                    [balance_amount15] )) p1

Have you tried running the convert queries in isolation? I see 3 convert queries, is rw alway 2 characters? — Aman B, Mar 07 '18 at 11:55
@AmanB Yes rw is always 2 (the highest any one person get is 15 so it will always be 2), and yes I ran the convert queries in isolation. The problem is this line: CONVERT(NVARCHAR(13), Cast( cc.balance_amount AS decimal(5,2))) AS balance_amt which is giving me the problem — awilso11, Mar 07 '18 at 14:06

score 0 · Answer 1 · answered Mar 07 '18 at 16:06

0

You need change your cast to Cast(cc.balance_amount AS decimal(11,6))

Decimal data types are defined as below:

DECIMAL(Precision,Scale )

Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2.

For more info see https://learn.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql

answered Mar 07 '18 at 16:06

Aman B

2,276
1
18
26

Thanks but I actually did have it as "cast(cc.balance_amount as decimal(11,6)" before posting this and still get the MSG 8115 Arithmetic overflow error – awilso11 Mar 07 '18 at 16:18
have you tried just casting it to decimal cast(cc.balance_amount as decimal)? – Aman B Mar 07 '18 at 16:26
or try convert instead of cast – Aman B Mar 07 '18 at 16:26
yes, If I do that, I will get a new error MSG 8167 stating that the column type for balance_amt conflicts with other columns – awilso11 Mar 07 '18 at 16:29
using just the convert gives the Arithmetic overflow error as well – awilso11 Mar 07 '18 at 16:30

Msg 8115 Arithmetic overflow error when I am trying to convert a balance amount

1 Answers1