-1

This may seem very simple but I can't figure it out. I have a field that is datatype bigint and I'm trying to multiply it by another field that is decimal(9,5) but I just get null in all of my calculations. I've tried casting the Bigint as decimal and it also does not work. What am I missing?

I am using SQL Server 2000 I believe. I'm referencing the number from two different tables joined on a common field.

((cast([costs] as decimal(9,5)) * cast([Ratio] as decimal(9,5))) as [Cost Ratio]

I also added isnull(ratio,0) and now it populates my null values with 0.00000 but still don't get the multiplied value in the field for [cost ratio].

enter image description here

Michael Tsu
  • 49
  • 1
  • 10
  • 1
    Which RDBMS do you use? – Jeroen Mar 21 '16 at 21:04
  • Also, a [mcve] might help. If I assume MSSQL 2014 and [try `SELECT CAST(3 AS BIGINT) * CAST(2.5 AS DECIMAL(9,5))` it works as expected](http://sqlfiddle.com/#!6/9eecb7/6614/0). – Jeroen Mar 21 '16 at 21:06
  • It would be nice it you put the formula you use and how you cast the values. – Erick Kamamba Mar 21 '16 at 21:06
  • Could it be that one of the values in your columns is `NULL`? This would be the most likely case. If the resulting type can not contain the calculated result you will get an exception in sql server. – Igor Mar 21 '16 at 21:10
  • Please include a [Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). You are asking us to guess as to what your inputs are and guess as to what the outputs are (you supply neither). Until you present something that can be reproduced we can't help you. – Igor Mar 21 '16 at 21:16
  • I certainly looks like you have a null value if using `isnull` caused the evaluation to go from nulls to zeroes. Use `select @@version` to find out for sure although I doubt it matters much. – shawnt00 Mar 21 '16 at 21:17
  • I'm using Microsoft SQL Server 2012, sorry for the wrong version. thanks Shawnt for the help – Michael Tsu Mar 21 '16 at 21:23
  • `costs` is the `bigint`? Why are you casting `ratio` if it's already `decimal(9, 5)`? – shawnt00 Mar 21 '16 at 21:28
  • I was casting it because my results kept coming out null so I tried to see if the ratio was the problem. – Michael Tsu Mar 21 '16 at 21:33
  • @michael I get an "Arithmetic overflow error converting bigint to data type numeric". Are you doing something to suppress that error? Like setting `ARITHABORT OFF` or `ARITHIGNORE ON`? – Salman A Mar 21 '16 at 21:38
  • Igor is right. By doing the select isnull I found my decimal(9,5) was not correct. I went back and checked the field and it was vachar(50) and the field above it was (9,5). Sorry for being such a noob. – Michael Tsu Mar 21 '16 at 21:42

1 Answers1

2

You should be getting arithmetic exceptions, not a null value. You are casting your BigInt values to decimal(9,5) which has precision = 9 scale = 5 which leaves only 4 digits on the left side of the decimal. All your values in your BigInt column are at least 6 digits or larger. This means there is not enough room in the decimal(9,5) to store that big int value. Try decimal(18,5), which leaves plenty of room on the left side of the decimal, for your types in your query.

Also do not forget ISNULL (where the value can be null in the data) and expect that if it is null then you are going to get a result of 0.

((cast([ISNULL(costs,0)] as decimal(18,5)) * cast([ISNULL(Ratio,0)] as decimal(18,5))) as [Cost Ratio]

Also if your Ratio column type is already a decimal(9,5) then do not convert it again, do add the ISNULL check though if it is a nullable column.

See: Sql Server Decimals for more details on the sql server decimal type. The reason I chose 18 as the precision has to do with storage.

Igor
  • 60,821
  • 10
  • 100
  • 175
  • So I tried this an something changed, now I get all 0.000000000 in my results column. Does that mean SQL thinks one of my fields is null? – Michael Tsu Mar 21 '16 at 21:34
  • Well, `0 * any value` is always 0 with the exception of any value being `NULL`. – Igor Mar 21 '16 at 21:35
  • If you are not sure which column it is then change the select by splitting them to find out. `((cast([ISNULL(costs,0)] as decimal(18,5)) AS LeftSide, cast([ISNULL(Ratio,0)] as decimal(18,5))) AS RightSide` – Igor Mar 21 '16 at 21:36
  • I changed the cast to ((cast([ISNULL(costs,0)] as decimal(18,5)) * cast([ISNULL(Ratio,1)] as decimal(18,5))) as [Cost Ratio] and all my results populated with '1'. But i can see in my table my ratio is decimal(9,5) – Michael Tsu Mar 21 '16 at 21:37
  • Thanks @Igor, checked the fields again due to the isnull() and found I had updated the wrong data field. Using the correct one now and it is casting with the decimal(18,5) – Michael Tsu Mar 21 '16 at 21:43