November 28, 2007 at 12:29 pm
November 28, 2007 at 1:33 pm
either declare @radius as a varchar (if you're not using it anywhere else), or CAST it to varchar in the set @sql statement.
either:
declare @sql nvarchar(4000)
declare @radius varchar(10)
set @radius = '6378.1'
-- error with this statement?
set @sql='SELECT lis_id, distance = ' + @radius + ' FROM Listings'
EXEC sp_executesql @sql
or
declare @sql nvarchar(4000)
declare @radius float
set @radius = 6378.1
-- error with this statement?
set @sql='SELECT lis_id, distance = ' + cast(@radius as varchar(10)) + ' FROM Listings'
EXEC sp_executesql @sql
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 28, 2007 at 1:58 pm
The conversion caused the radius value to lose precision:
declare @sql nvarchar(4000)
declare @radius float
set @radius = -0.256712789599778
set @sql='SELECT lis_id, distance = ' + cast(@radius as varchar(20)) + ' FROM Listings'
EXEC sp_executesql @sql
distance was set to -0.256713 rather than -0.256712789599778
Also what is the difference between cast(@radius as varchar(10)) and convert(varchar(10),@radius) ?
Thanks,
Ham
November 28, 2007 at 4:15 pm
if you don't specify a style, your CAST syntax, and your CONVERT syntax should both return only 6 digits.
If on the other hand you try this:
convert(char,@radius,2) --the 2 is the "style" component, telling it to use 16 digits
you shouldn't lose any precision, if you type it as a FLOAT(53) (that is maximum precision).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 28, 2007 at 6:47 pm
The conversion caused the radius value to lose precision:
Heh... who cares about the precision... using FLOAT, you've lost some accuracy. Think not? Try this...
DECLARE @Radius FLOAT(53)
SET @Radius = .1
SELECT @Radius
Yes, yes... Float is great for scientific calculations... but, you better know what you're doing with FLOAT or you'll crash another Martian Lander 😛
Also, someone really needs to tell me how you can have a negative Radius... must be "black hole theory" or something...:hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
November 28, 2007 at 9:06 pm
Heh... who cares about the precision... using FLOAT, you've lost some accuracy. Think not? Try this...
You have not lost anything.
You just display the same value in different representation.
Think not?
Check it out.
If you want to compare the values you need to compare the same data types. Because presentation in QA or EM is defined by data type interpretation.
What is "0.1" you set to @Raduis?
Let's see:
[Code]
SELECT SQL_VARIANT_PROPERTY(0.1, 'BaseType'), SQL_VARIANT_PROPERTY(0.1, 'Precision'), SQL_VARIANT_PROPERTY(0.1, 'Scale')
[/Code]
It returns:
[Code]numeric 11[/Code]
So, the last statement must be
[Code] SELECT CONVERT(numeric(1,1), @Radius)
[/Code]
See any difference?
I don't.
_____________
Code for TallyGenerator
November 28, 2007 at 10:15 pm
No, no... not that, Serqiy... I understand that.
I meant this type of accuracy when I said you better know what you're doing...
CREATE TABLE #Test(MyFloat FLOAT(53), MyDecimal DECIMAL(38,37))
INSERT INTO #Test (MyFloat,MyDecimal)
SELECT 1.0/N,1.0/N
FROM dbo.Tally
WHERE N<=20
SELECT *
FROM #Test
WHERE MyFloat = MyDecimal
--Jeff Moden
Change is inevitable... Change for the better is not.
November 28, 2007 at 10:28 pm
use
convert(char(20),@radius)
November 29, 2007 at 1:43 am
Jeff Moden (11/28/2007)
No, no... not that, Serqiy... I understand that.I meant this type of accuracy when I said you better know what you're doing...
In fact what you are doing is implicit conversions.
[Code] SELECT
SQL_VARIANT_PROPERTY(1.0/N, 'BaseType'),
SQL_VARIANT_PROPERTY(1.0/N, 'Precision'),
SQL_VARIANT_PROPERTY(1.0/N, 'Scale')
FROM dbo.Tally
WHERE Number<=20
[/Code]
I'm not sure everyone can see from this code what it's really doing.
_____________
Code for TallyGenerator
November 29, 2007 at 8:36 am
Yep, I understand that... I think we're both talking about the same thing... just a different way...
I didn't say it quite right when I spoke of "accuracy" and I appologize... what I meant was that you have to understand how FLOAT works to use it effectively. If you're expecting the answers from DECIMAL calcs to match those of FLOAT calcs, you'll be surprised. You do have to take some post-calulation action as you suggested in one of your previous posts.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 29, 2007 at 8:24 pm
If you're expecting the answers from DECIMAL calcs to match those of FLOAT calcs, you'll be surprised. You do have to take some post-calulation action as you suggested in one of your previous posts.
Well, I'm not gonna be surprised. 😉
They MUST be different. Don't want to explain why, you should have an idea.
But it has nothing to do with FLOAT datatype.
What probably will surprise you is the fact that results from 2 DECIMAL calculations do not match if different precision/scale are used.
[Code]
CREATE TABLE #Test(SmallDecimal DECIMAL(38,37), BigDecimal DECIMAL(38,37))
INSERT INTO #Test (SmallDecimal, BigDecimal)
SELECT 1.0/N, 1.000000000000000/N
FROM dbo.Tally
WHERE N > 0 AND N<=20
SELECT *
FROM #Test
WHERE SmallDecimal = BigDecimal
[/Code]
Well, 1st number has just 12 significant decimal digits, and if we'll match precisions they suppose to be identical.
Not really.
Try this:
[Code]
SELECT CONVERT(DECIMAL(13, 12), SmallDecimal), CONVERT(DECIMAL(13, 12), BigDecimal)
FROM #Test
WHERE CONVERT(DECIMAL(13, 12), SmallDecimal) <> CONVERT(DECIMAL(13, 12), BigDecimal)
[/Code]
So, as you can see, it's not a FLOAT problem.
Any calculation (including conversions) on any decimal number makes last digit unfaithful.
And if you display this digit or rely on it in further calculations you possibly introduce an error.
select 1./3 + 1./3 + 1./3
_____________
Code for TallyGenerator
November 29, 2007 at 8:46 pm
Another surprising for someone result comes from simple primary school exercise.
[font="Courier New"]X / N * M = X * M / N[/font]
Is it true?
Let's check.
[Code]
declare @D decimal(18,15), @F FLOAT
SET @D = 1 SET @F = 1
SELECT @D / 333 * 777, @D * 777 / 333, @F / 333 * 777, @F * 777 / 333
SELECT @D / 666 * 777, @D * 777 / 666, @F / 666 * 777, @F * 777 / 666
[/Code]
What do you see?
It's true if you use FLOAT, and false if you use DECIMAL.
DECIMALs just don't bring consistent result.
Thar's why you should always use FLOAT for calculations.
Just don't forget to cut off the last digit from the result.
😎
_____________
Code for TallyGenerator
November 30, 2007 at 6:18 am
Why do we need to convert when its not required....
DECLARE @radius float
SET @radius = -0.256712789599778
SELECT lis_id, @radius AS distance FROM Listings
--Ramesh
November 30, 2007 at 10:34 am
Another surprising for someone result comes from simple primary school exercise.
X / N * M = X * M / N
Is it true?
( :blush: Wait a minute while I get my foot out of my mouth 😀 ...mumble...mumble...yank...yank...POP!)
Holy smokes! Now, THAT's an interesting proof! Thanks, Serqiy.
Thar's why you should always use FLOAT for calculations.
Just don't forget to cut off the last digit from the result.
After seeing that, I gotta agree! What's really amazing is how far off the DECIMAL calcs can actually be at the lower scales...
DECLARE @D DECIMAL(18,2),
@F FLOAT
SET @D = 1
SET @F = 1
SELECT @D / 666 * 777, @D * 777 / 666, @F / 666 * 777, @F * 777 / 666
-------- -------- ------------------ ------------------
1.166277 1.166666 1.1666666666666667 1.1666666666666667
(1 row(s) affected)
DECLARE @D MONEY,
@F FLOAT
SET @D = 1
SET @F = 1
SELECT @D / 666 * 777, @D * 777 / 666, @F / 666 * 777, @F * 777 / 666
------ ------ ------------------ ------------------
1.1655 1.1666 1.1666666666666667 1.1666666666666667
(1 row(s) affected)
It's interesting that ROUND does not appear to be the correct thing to use to save/display the result with the desired scale but what's REALLY interesting is how far off the DECIMAL answer can be in the most unassuming of calculations due to lost precision and scale...
DECLARE @F FLOAT
SET @F = .3333
DECLARE @D DECIMAL(4,2)
SET @D = .3333 --Simulates precision/scale lost during a calculation
SELECT N,
[N*@F] = N*@F,
[N*@D] = N*@D,
[STR(N*@F,4,2)] = STR(N*@F,4,2),
[ROUND(N*@F,2)] = ROUND(N*@F,2),
[CONVERT(DECIMAL(4,2),N*@F)] = CONVERT(DECIMAL(4,2),N*@F)
FROM dbo.Tally
WHERE N <= 10
N N*@F N*@D STR(N*@F,4,2) ROUND(N*@F,2) CONVERT(DECIMAL(4,2),N*@F)
-- ------------------- ---- ------------- ------------------- --------------------------
1 0.33329999999999999 .33 0.33 0.33000000000000002 .33
2 0.66659999999999997 .66 0.67 0.67000000000000004 .67
3 0.99990000000000001 .99 1.00 1.0 1.00
4 1.3331999999999999 1.32 1.33 1.3300000000000001 1.33
5 1.6664999999999999 1.65 1.67 1.6699999999999999 1.67
6 1.9998 1.98 2.00 2.0 2.00
7 2.3331 2.31 2.33 2.3300000000000001 2.33
8 2.6663999999999999 2.64 2.67 2.6699999999999999 2.67
9 2.9996999999999998 2.97 3.00 3.0 3.00
10 3.3329999999999997 3.30 3.33 3.3300000000000001 3.33
So, yeah... you're spot on... do the calculations in FLOAT... save/display the result with a conversion of the desired scale.
Man, thanks for taking the time for the outstanding lesson, Serqiy... I've always been against the idea of using FLOAT calculations because I didn't follow the first rule of computing... "A Developer must not guess... a Developer must KNOW." Heh... I wonder where I got that from 😉
Now, if you'll excuse me... I have to go get the mouthwash... that damned "Don't use FLOAT" shoe has been stuck in my mouth so long, I forgot what the flavor of real computer math is :sick:
--Jeff Moden
Change is inevitable... Change for the better is not.
November 30, 2007 at 10:41 am
Heh... oh yeah... almost forgot...
"NO... I am NOT smarter than a 5th grader!" 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply