四捨五入不進位??

人客反應一個四捨五入到小數二位的問題...
我沒什麼頭緒要怎麼解決, 但在不小心中發現一個方法,暫時可以解決四捨五入不進位的問題
這是sql server 的float bug嗎, 還是本來就這樣??

二組數字分別是1.055, 1.1243,型態是float
依四捨五入到小數第二位的話,  結果應該是  c1 =  1.06  c2= 1.12

但結果卻不如人意,而且只有當此float 刪好只有到小數第三位時,欲四捨五入進位到小數二位才會有問題

先reproduce一下狀況..

declare @tab table (c1 float,c2 float)
insert into @tab
select 1.055, 1.1243

select 
c1,c2
,cast(c1 as decimal(5,2)) as r1,cast(c2 as decimal(5,2)) as r2
,cast(c1 + 0.001 as decimal(5,2)) as t1,cast(c2 + 0.001 as decimal(5,2)) as t2
,cast(c1 + 0.0001 as decimal(5,2)) as s1,cast(c2 + 0.0001 as decimal(5,2)) as s2
 from @tab
但結果如下

c1	c2	r1	r2	t1	t2	s1	s2
1.055	1.124	1.05	1.12	1.06	1.13	1.06	1.12

有趣的是, 1.124 + 0.001 =  1.125 , 轉成decimal(5,2)後,又很精準的四捨五入了, 但結果是不對的....


所以再做下列測試
如果c1為1.0551 又或者將型態改成decimal(5,4)  則結果是正確的

declare @tab table (c1 float,c2 float)
insert into @tab
select 1.0551, 1.124

select  
c1,c2
,cast(c1 as decimal(5,2)) as r1,cast(c2 as decimal(5,2)) as r2
,cast(c1 + 0.001 as decimal(5,2)) as t1,cast(c2 + 0.001 as decimal(5,2)) as t2
,cast(c1 + 0.0001 as decimal(5,2)) as s1,cast(c2 + 0.0001 as decimal(5,2)) as s2
 from @tab

c1	c2	r1	r2	t1	t2	s1	s2
1.0551	1.124	1.06	1.12	1.06	1.13	1.06	1.12

declare @tab table (c1 decimal(5,4),c2 decimal(5,4))
insert into @tab
select 1.055, 1.124

select  
c1,c2
,cast(c1 as decimal(5,2)) as r1,cast(c2 as decimal(5,2)) as r2
,cast(c1 + 0.001 as decimal(5,2)) as t1,cast(c2 + 0.001 as decimal(5,2)) as t2
,cast(c1 + 0.0001 as decimal(5,2)) as s1,cast(c2 + 0.0001 as decimal(5,2)) as s2
 from @tab

c1	c2	r1	r2	t1	t2	s1	s2
1.055	1.124	1.06	1.12	1.06	1.13	1.06	1.12

將小數點變成四位後,四捨五入到小數二位就對了

再來一個測試

declare @tab table  (c1 float,c2 float)
 insert into @tab
select 1.055, 1.124

select  
c1,c2
,cast(c1+0.000 as decimal(5,2)) as r1,cast(c2 as decimal(5,2)) as r2
,cast(c1 + 0.001 as decimal(5,2)) as t1,cast(c2 + 0.001 as decimal(5,2)) as t2
,cast(c1 + 0.0001 as decimal(5,2)) as s1,cast(c2 + 0.0001 as decimal(5,2)) as s2
 from @tab

c1	c2	r1	r2	t1	t2	s1	s2
1.055	1.124	1.05	1.12	1.06	1.13	1.06	1.12

加上0.000是沒有用的...

結論:

1.float使用真的要小心謹慎,不是那麼精準

2.四捨五入到小數二位, 則先加0.0001變成有小數四位後,再進行四捨五入

大家有看懂嗎???

Informix執行計劃

執行SQL前,先執行下列
SET EXPLAIN ON  , 開啟執行計劃, 要整個跑完後才能看
SET EXPLAIN OFF, 關閉執行計劃
SET EXPLAIN ON AVOID_EXECUTE, 開始執行計劃,但只評估不執行

所以查看SQL 用到index的狀況,可選擇第3種方式
詳細使用可查看informix online help

完成後會輸出到 sqexplain.out
vi 查看內容如下

QUERY:
------
select * from test
Estimated Cost: 453
Estimated # of Rows Returned: 8835
  1) xxxx.test: SEQUENTIAL SCAN

QUERY:
------
select * from ss_code
Estimated Cost: 453
Estimated # of Rows Returned: 8835
  1) xxxx.test: SEQUENTIAL SCAN
~
~
"sqexplain.out" 21 lines, 263 characters

Could not load file or assembly 'ReportingServicesNativeClient

在Windows 2008 R2上安裝 SQL Server 2005 64位元
Reporting Server 網站進入時出現下列錯誤
Could not load file or assembly 'ReportingServicesNativeClient, Version=0.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. An attempt was made to load a program with an incorrect format.
找到討論回文,照做後,問題解決轉貼abhijitc的回文
 The problem is not SSRS, the problem is asp runing on 32-64 bits. to enable the SSRS follow these steps:
1. To run the 64-bit version of ASP.NET 2.0, Click Start, click Run, type cmd, and then click OK.

2. Type the following command to disable the 32-bit mode:
cscript %SYSTEMDRIVE%\inetpub\adminscripts\adsutil.vbs SET W3SVC/AppPools/Enable32bitAppOnWin64 0
3. Type the following command to install the version of ASP.NET 2.0 and to install the script maps at the IIS root and under:
%SYSTEMROOT%\Microsoft.NET\Framework64\v2.0.50727\aspnet_regiis.exe -i
4. Make sure that the status of ASP.NET version 2.0.50727 is set to Allowed in the Web service extension list in Internet Information Services Manager.

5. Check the SSRS configuration

Note The build version of ASP.NET 2.0 may differ depending on what the currently released build version is. These steps are for build version 2.0.50727.

view this link : http://support.microsoft.com/kb/894435/en-us