Solved

Why do these yield different results ?

Posted on 2009-05-18
11
382 Views
Last Modified: 2012-06-21
As you can see by the code the latter query has more decimals why cant I get the first query to yield the same result ?
SELECT A,B,A/B FROM
(
     SELECT colA, colB FROM thetable
) C
		
SELECT A,B,A/B FROM
(				
     SELECT		1.00000000000000000000 AS A, 1.40673489600528227000 AS B
) C
 
And the Result
 
1.00000000000000000000	1.40673489600528227000	0.710865
1.00000000000000000000	1.40673489600528227000	0.71086599389814597

Open in new window

0
Comment
Question by:Pall Palsson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 24418247
my *guess* is that it has something to do with the datatypes in thetable.
I can't explain why from your results but:
select 1.00000000000000000000/convert(float,1.40673489600528227000) = 0.710865993898146
and
select 1.00000000000000000000/convert(real,1.40673489600528227000) = 0.710866
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24418349
it is completely depends on the datatype you use in your table or in your calculation. have a look at following queries and its results printed after query.



SELECT A,B,A/B FROM
(                               
     SELECT             1.00000000000000000000 AS A, 1.40673489600528227000 AS B
) C --0.71086599389814597
 
 
SELECT A,B,A/B FROM
(                               
     SELECT             1.00000000000000000000 AS A, 1.40 AS B
) C --0.714285714285714285714285
 
SELECT A,B,A/B FROM
(                               
     SELECT             1 AS A, 1.40 AS B
) C --0.714285

Open in new window

0
 
LVL 4

Expert Comment

by:TimSledge
ID: 24418494
Use the format below to make sure division occurs properly and to control decimal output of the result:
SELECT convert(decimal(18,2), (convert(decimal(18,2), A)  /  convert(decimal(18,2), D) ) )

See http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_24416319.html
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:Pall Palsson
ID: 24420731
Hi, both fields (ColA and ColB) are decimal(38, 20)

If you look at the results  the upper line is the result from the Columns and the lower from just typing in the values in the select.

If I use + or - i.e. A+B or A-B I get the correct amount of decimals.  If I try to use Cast or Convert to decimal(38, 20) I Get the same result 0.710865 (0.71086500000000000)

If I multiply or divide the cutoff happens.

So neither of your proposals work.
1.00000000000000000000  1.40673489600528227000  0.710865
1.00000000000000000000  1.40673489600528227000  0.71086599389814597

Open in new window

0
 
LVL 4

Expert Comment

by:TimSledge
ID: 24420902
This should result in the same number of decimal places...

SELECT A,B,convert(decimal(38,20), A/B)  FROM
(
     SELECT colA, colB FROM thetable
) C
            
SELECT A,B,convert(decimal(38,20), A/B)  FROM
(                        
     SELECT            1.00000000000000000000 AS A, 1.40673489600528227000 AS B
) C
0
 

Author Comment

by:Pall Palsson
ID: 24421057
Tim:

What you propose results in 0.71086500000000000 which is not the same as 0.71086599389814597

Thus it doesnt work
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24421070
what about this one?


SELECT A,B,convert(decimal(38,20), A)/convert(decimal(38,20), B)  FROM
(
     SELECT colA, colB FROM thetable
) C
     
0
 
LVL 4

Expert Comment

by:TimSledge
ID: 24421550
The code below tells the equation to limit B to 2 decimal places and should create the same result.   You actually need to do this for A as well.  The only way you can get the same result is to use the same number of decimal places for B each time and for A each time. This is back to my original response.

SELECT A,convert(decimal(38,2), B),convert(decimal(38,20), A/B)  FROM
(
     SELECT colA, colB FROM thetable
) C
           
SELECT A,convert(decimal(38,2), B),convert(decimal(38,20), A/B)  FROM
(                        
     SELECT            1.00000000000000000000 AS A, 1.40673489600528227000 AS B
) C
0
 

Author Comment

by:Pall Palsson
ID: 24423762
Hi,

I have tried to to use convert(decimal(38,20) AND cast like this X() represents what i tried to convert/cast

X(A)/B
X(A)/X(B)
A/X(B)
X(X(A)/X(B))
X(A/X(B))
X(X(A)/B)

with no success.

However QPR's solution of using float comes closest to yielding the correct result.
cast/convert   result
correct:       0.71086599389814597
float:         0.71086599389814600
decimal:       0.71086500000000000
noone:         0.710865

Open in new window

0
 
LVL 4

Accepted Solution

by:
TimSledge earned 500 total points
ID: 24423868
I just realized you need to use  Convert(Decimal(32,1) to get the same results.  When you use anything higher than 1 in the 2nd position (controls how many places to the right of the decimal ) you are not multiplying by the same numbers.

You can never get the same answer when multiplying by 1.4 one time then 1.40673489600528227000 another time.  

Convert(Decimal(TotalDigits,DigitsToRightOfDecimal) ItemToConvert) is a way to make the two multipliers the same.  Convert(Decimal(32,1), 1.4) and Convert(Decimal(32,1), 1.40673) will both multiply by 1.4.

If you go one step higher (32,2), you will be multiplying by 1.40 and 1.41 (1.406 rounded to two places).

Try the earlier suggestion, but use 32,1 instead of 32,2.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
TSQL Challenge... 7 44
query output (script) from a stored procedure 4 39
Finding Where Clause Value in SQL Views and SP 21 42
SQL query 7 18
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question