May 2, 2011 at 6:25 am
Hi,
When performing Arithmetic calculation in SQL server, The Outcome is not matching in any case with the Application front end result. Let me explain the issue with an Example
There are 2 Columns in a table which are participating in calculation and outcome is being stored in 3rd column of same table.
Formula used: Column C = Value in Column A * (1- (Value in ColumnB/100))
Data Types
Column A = Float
Column B = Float
Column C = Decimal (38,0)
Table ABCD
--------------------------------------------
Record_No Column A Column B ColumnC
1 147101.491101491 164.3 -94586
2 1684988.93698894 1188 -18332680
3 639700327.080327 7251.1 -45745610090
4 25510045504569.5 10000000 -2550979040411445200
Calculation is happening correctly for all records except Record Number 4.
For Record Number 4:
When performing this Formula over calculator I am getting result as
-2550979040411445430.5
However Using Below Script I am getting -2550979040411445200 for Record No 4.
select
CASE
WHEN A IS NULL AND B IS NULL
THEN NULL
ELSE ( CONVERT(Decimal(38,0),ISNULL(A,0) *(1-ISNULL((B/100),0))) )
END AS C
FROM dbo.ABCD.
PLease note that the Result which is appearing in Front end is
-2550979040411450000.000 i.e. also differs from Calculator result.
Can somebody tell me the reason why this is happening and How can we short out the issue.
Please help me out soon as its a only issue blocking our deployment to UAT and Production.
Regards,
Amit M.
May 2, 2011 at 6:30 am
Values in Table ABCD
A B C
================================================================
147101.491101491 | 164.3 | -94586
1684988.93698894 | 1188 | -18332680
639700327.080327 | 7251.1 | -45745610090
25510045504569.5 | 10000000 | -2550979040411445200
May 2, 2011 at 10:21 am
You don't have any numbers to the right of the decimal so your number is being truncated.
Decimal (38,0)
You will need to change this datatype in order to get decimal place precision.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2011 at 10:34 am
The other problem is that you are using float as your datatype which is an approximate datatype and your value on record 4 is very large. Try changing the column to an exact numeric type (like decimal). You can read up on datatypes here
Here is an example of your numbers being approximated.
select cast(25510045504569.5 as float) * (1 - 10000000) --notice this your float
Here it is with a decimal.
select cast(25510045504569.5 as decimal(38,2)) * (1 - 10000000)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply