November 21, 2007 at 11:59 pm
hi everybody,
this is really blowing my mind :crazy:
i'm converting a vb routine to ms sql server 2000 for and i'm having a problem with a simple math operation.
here is a scratch from the vb code:
Matrix(i, j) = Matrix(i, j) + (Matrix(P, j) * -m)
the values for Matrix(i, j) is 2.77555756156289E-17
the value for Matrix(P, j) is 15
the value of m is 1.85037170770859E-18
so, when executing vb code i get back 3.85185988877447E-34 as result
then i move vb code to sql server
SELECT @Tmp = measureData FROM dbo.New_AMatrix WHERE x = @i AND y = @j-2
SELECT @tmp2 = measureData FROM dbo.New_AMatrix WHERE x = @P AND y = @j-2
SET @NewValue = @Tmp + (@tmp2 * @m)
here is the sql output
@m -> -1.85037e-018
Matrix(i, j) -> 2.77556e-017
Matrix(P, j) -> 15
@NEwValue -> 0
here is variable declaration
DECLARE @Tmp REAL
DECLARE @tmp2 REAL
DECLARE @NewValue REAL
does anyone can help me?
thank you in advance for any suggest
with my regards,
max
November 22, 2007 at 12:22 am
You have not initialized any value for variable @m....
DECLARE @Tmp REAL
DECLARE @tmp2 REAL
DECLARE @NewValue REAL
DECLARE @m REAL
SELECT @Tmp = measureData FROM dbo.New_AMatrix WHERE x = @i AND y = @j-2
SELECT @tmp2 = measureData FROM dbo.New_AMatrix WHERE x = @P AND y = @j-2
SET @m = 1.85037170770859E-18
SET @NewValue = @Tmp + (@tmp2 * @m)
SELECT @NewValue
Do you really need that many digits of precision? You could even use NUMERIC or DECIMAL types to the required precision.
--Ramesh
November 22, 2007 at 12:41 am
here's the whole stored procedure.
sorry i copy the wrong variables declaration ( real instead of float )
yes, unfortunately i do need that precision as the request is to compute linear regression.
CREATE PROCEDURE dbo.CDSem_BestFocus_GaussJordan
AS
DECLARE @P INT
DECLARE @i INT
DECLARE @j-2 INT
DECLARE @m FLOAT
DECLARE @Pivot FLOAT
DECLARE @d FLOAT
DECLARE @Tmp FLOAT
DECLARE @tmp2 FLOAT
DECLARE @NewValue FLOAT(53)
SET @P = 1
SET @i = 1
SET @j-2 = 1
WHILE @P <= 2
BEGIN
SELECT @Pivot = measureData FROM dbo.New_AMatrix WHERE x = @P AND y = @P
WHILE @i <= 2
BEGIN
IF NOT @P = @i
BEGIN
SELECT @Tmp = measureData FROM dbo.New_AMatrix WHERE x = @i AND y = @P
SET @m = -@m
SET @Tmp = 0
SET @tmp2 = 0
WHILE @j-2 <= 5
BEGIN
PRINT '@i -> ' + convert (varchar ,@i)
PRINT '@j -> ' + convert (varchar ,@j)
PRINT '@P-> ' + convert (varchar ,@P)
SELECT @Tmp = measureData FROM dbo.New_AMatrix WHERE x = @i AND y = @j-2
SELECT @tmp2 = measureData FROM dbo.New_AMatrix WHERE x = @P AND y = @j-2
SET @NewValue = @Tmp + (@tmp2 * @m)
UPDATE dbo.New_AMatrix SET measureData = @NewValue WHERE x = @i AND y = @j-2
PRINT '@m -> ' + convert( varchar , @m)
PRINT 'Matrix(i, j) -> ' + convert (varchar ,@tmp)
PRINT 'Matrix(P, j) -> ' + convert (varchar ,@tmp2)
PRINT @NewValue
SET @Tmp = 0
SET @tmp2 = 0
SET @NewValue = 0
END
END
SET @j-2 = 1
SET @i = @i + 1
END
SET @i = 1
SET @P = @P + 1
END
SELECT * FROM New_AMatrix
GO
November 22, 2007 at 7:08 am
Hi
I checked the numbers in your original post.
When I used:
declare @x float
set @x=2.77555756156289E-17
declare @y float
set @y = 15
declare @m float
set @m = 1.85037170770859E-18
declare @result float
--Matrix(i, j) + (Matrix(P, j) * -m)
set @result= (@x) + (@y * -@m)
print '@result= ' + convert(varchar(50),@result)
I got:
@result= 5.23853e-032
Not your value from VB but...
What happens if you use float and not real?
November 22, 2007 at 7:27 am
The vb code you posted uses:
Matrix(i, j) = Matrix(i, j) + (Matrix(P, j) * -m)
... is this minus m? You don't seem to use it in the SQL...
November 22, 2007 at 7:35 am
sorry i wrote some incorrect info in my first post.
the correct vars declare is
DECLARE @Tmp FLOAT
DECLARE @tmp2 FLOAT
DECLARE @NewValue FLOAT(53)
the @m is set to minus:
SELECT @Tmp = measureData FROM dbo.New_AMatrix WHERE x = @i AND y = @P
SET @m = -@m
please refer to the 3rd post that is the right one!
thank u
Regards
Max
November 22, 2007 at 7:35 am
I was just trying your example, here is what i found.
VB : 5.23853e-032
SQL (with real) : 0
SQL (with float) : 5.23853e-032
This says, that somehow the real data type is not accepting the values which are in its range. (BOL says that real data type can hold data between - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38)
--Ramesh
November 22, 2007 at 7:42 am
I swear vb result is 3.85185988877447E-34 !! (how can i attach an image to a new post?)
SQL result is 0
I'm actually using FLOAT instead REAL.
Thank you
Regards,
Max
November 22, 2007 at 7:57 am
DECLARE @fX FLOAT, @fY FLOAT, @fM FLOAT
DECLARE @Rx REAL, @rY REAL, @rM REAL
SELECT @fX = 2.77555756156289E-17, @fY = 15, @fM = 1.85037170770859E-18
SELECT @Rx = 2.77555756156289E-17, @rY = 15, @rM = 1.85037170770859E-18
SELECT @fX + ( @fY * -@fM ) AS [Using Float], @Rx + ( @rY * -@rM ) AS [Using Real]
I got 5.23852944873328E-32 & 0 resp.
--Ramesh
November 22, 2007 at 8:09 am
I do not have the same result in sql using float data type.
i got 0 back.
i checked also @@MAX_PRECISION is = to 38
regards,
max
November 22, 2007 at 8:20 am
max (11/22/2007)
I swear vb result is 3.85185988877447E-34 !! (how can i attach an image to a new post?)SQL result is 0
I'm actually using FLOAT instead REAL.
Thank you
Regards,
Max
When you create a new post, scroll down far enough to see the "Edit Attachements" button...
Just to confirm what Ramesh and others have already discovered... Using the numbers from the first example...
[font="Courier New"]-- Matrix(i, j) = Matrix(i, j) + (Matrix(P, j) * -m)
-- 
-- the values for Matrix(i, j) is 2.77555756156289e-17
-- the value for Matrix(P, j) is 15
-- the value of m is -1.85037170770859e-18
-- 
-- so, when executing vb code i get back 3.85185988877447E-34 as result
DECLARE @MATij FLOAT(53)
DECLARE @MATpj FLOAT(53)
DECLARE @m     FLOAT(53)
    SET @MATij = 2.77555756156289E-17
    SET @MATpj = 15
    SET @m     = 1.85037170770859E-18
 SELECT @MATij + (@MatPJ * -@m)
5.2385294487332815E-32
(1 row(s) affected)[/font]
... I'd like to suggest that the error has been made on the VB side of the house and not the SQL Server side. You can verify these findings using any good scientific calculator...
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2007 at 8:59 am
I understand that your VB code says 3.8...
But - just put this into Excel and you get:
5.23853E-32
You got something stinky happening in the VB code....
----------------------------------------------------------------------------------
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 22, 2007 at 11:26 pm
well. it seems that we have trust vb result for years and now we discover that it was not right!
i'll try this also with c++ to see what is the output
thank you a lot for now
regards,
max
November 24, 2007 at 1:17 am
Max,
I confirm your results in VB. But I also tried to 'visualize' the math operation you are trying to perform. When you ditch the scientific notation and line 'em up, it looks like this:
[font="Courier New"]1 +00.0000000000000000277555756156289000000000000000 Matrix_ij=2.77555756156289E-17
2 +15.0000000000000000000000000000000000000000000000 Matrix_Pj=15
3 +00.0000000000000000018503717077085900000000000000 m=1.85037170770859E-18
4 -00.0000000000000000277555756156288000000000000000 Matrix_Pj * -m=-2.77555756156288E-17
5 +00.0000000000000000000000000000000516149225095779 result=5.16149225095779E-32 [/font]
Thus you are summing a number and its negative (1st and 4th values) which differ only in the last digit. Yet VB comes up with this watershed of digits immediately after the last significant digit of the two operands. How it does this is beyond me. How SQL Server gets its result is beyond me as well. In my book you should be getting 00.0000000000000000000000000000001 which is 1.0E-31. Anything else is a lie.
November 24, 2007 at 8:51 am
In my book you should be getting 00.0000000000000000000000000000001 which is 1.0E-31. Anything else is a lie.
Yes... I agree... in DECIMAL math, you would be absolutely correct. But FLOAT does not use Decimal math... it uses BINARY math... and there are many Decimal numbers that cannot be exactly represented by the scale of the registers in Binary.
For example... what's 1/10? Are you sure? I have proof otherwise 😉
[font="Courier New"]DECLARE @Dividend FLOAT(53)
DECLARE @Divisor FLOAT(53)
DECLARE @HardCode FLOAT(53)
SET @Dividend = 1
SET @Divisor = 10
SET @HardCode = .1
SELECT @Dividend, @Divisor, @HardCode, @Dividend/@Divisor[/font]
Even the hard-coded value of .1 was not able to be successfully represented as the exact value.
So, let's do another experiment, but this time with the numbers being used to solve this problem...
[font="Courier New"]DECLARE @MATij FLOAT(53)
DECLARE @MATpj FLOAT(53)
DECLARE @m FLOAT(53)
DECLARE @Result FLOAT(53)
SET @MATij = 2.77555756156289e-17
SET @MATpj = 15
SET @m = 1.85037170770859e-18
SELECT 'Entered',
'2.77555756156289e-17' AS '@Matij',
'15' AS '@MATpj',
'1.85037170770859e-18' AS '@m'
SELECT 'Actual',@MATij,@MATpj,@m[/font]
... see the difference?
[font="Courier New"]        @Matij                   @MATpj @m                   
------- ----------------------   ------ -------------------- 
Entered 2.77555756156289e-17     15     1.85037170770859e-18
Actual  2.7755575615628901E-17   15.0   1.85037170770859E-18
[/font]
Convert that to Decimal math and see what you get...
If you're gonna use FLOAT, you have to realize that it's NOT Decimal math and you must round the answer (and, maybe the interim answers) to the desired scale.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply