-1

There are two columns having datatype Decimal (16,4)

  1. OwnedShares
  2. TotalOutstandingShares

I am trying to compute percentage using these two columns and then converting it to Decimal(7,4).

Code Being used:

Convert(Decimal(7,4),Case when OwnedShares = 0 or TotalOutstandingShares = 0 then 0 else ((OwnedShares/TotalOutstandingShares)*100) end)

I am getting Error message : 'Arithmetic overflow error converting numeric to data type numeric.'

Can someone help to figure me out where am I going wrong?

Akina
  • 39,301
  • 5
  • 14
  • 25
Ankit Aakash
  • 13
  • 1
  • 2
  • 1
    Incorrect syntax. `CONVERT(expression, datatype)`, not backward. – Akina Aug 30 '21 at 09:16
  • @Akina, I don't think there is any error in Syntax. Syntax of CONVERT function in T-SQL is CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) . I verified this from https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15 – Ankit Aakash Aug 30 '21 at 10:13
  • :facepalm: If so then why the question was tagged as MySQL-related? removing unrelated tag... done. – Akina Aug 30 '21 at 10:31
  • 1
    Seems like you have a record with a large number of OwnedShares and a small number (but not zero) of TotalOutstandingShares. `sp_decribe_first_result_set` thinks you need a `decimal(38,18)` to hold the result of `(OwnedShares/TotalOutstandingShares)*100`. See also: [Precision, scale, and Length](https://learn.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql). – AlwaysLearning Aug 30 '21 at 10:58
  • Please provide enough code so others can better understand or reproduce the problem. – Community Sep 01 '21 at 11:03

1 Answers1

0

If you're getting an arithmetic overflow error, then it's likely that your percentage calculation is returning at least one record which is greater than 100.0000 (or 100%). Try increasing the decimal's precision to increase the number of digits that can be stored until the error no longer occurs, e.g. DECIMAL(8,4) or greater.

Deirdre O'Leary
  • 420
  • 2
  • 6