albert's blog

2015/08/03

java.lang.ArithmeticException: Non-terminating decimal expansion; no exact representable decimal result

Problem
Here is my code snippet.

If amtIn is 100 and amtOut is 20, then it works fine.

  private BigDecimal toRate(final BigDecimal amtIn, final BigDecimal amtOut) {
      if (null == amtIn || null == amtOut) {
          return null;
      }
      return amtIn.divide(amtOut).multiply(new BigDecimal(100));
  }

But if amtIn is 100 and amtOut is 120, then it will throw exception unexpectedly

16:40:15,812 INFO  [stdout] java.lang.ArithmeticException: Non-terminating decimal expansion; no exact representable decimal result.
16:40:15,812 INFO  [stdout]   at java.math.BigDecimal.divide(BigDecimal.java:1616) ~[na:1.7.0_25]
16:40:15,812 INFO  [stdout]   at gov.nta.dbm.service.Dbm034eService.toRate(Dbm034eService.java:264) ~[dbm-service-1.0.0-SNAPSHOT.jar:1.0.0-SNAPSHOT]
16:40:15,812 INFO  [stdout]   at gov.nta.dbm.service.Dbm034eService.modify(Dbm034eService.java:359) ~[dbm-service-1.0.0-SNAPSHOT.jar:1.0.0-SNAPSHOT

Solution
According to JavaDoc...
When a MathContext object is supplied with a precision setting of 0 (for example, MathContext.UNLIMITED), arithmetic operations are exact, as are the arithmetic methods which take no MathContext object. (This is the only behavior that was supported in releases prior to 5.)
As a corollary of computing the exact result, the rounding mode setting of a MathContext object with a precision setting of 0 is not used and thus irrelevant. In the case of divide, the exact quotient could have an infinitely long decimal expansion; for example, 1 divided by 3.
If the quotient has a nonterminating decimal expansion and the operation is specified to return an exact result, an ArithmeticException is thrown. Otherwise, the exact result of the division is returned, as done for other operations.

Owing to 100/120 has a nonterminating decimal expansion, so it will throw java.lang.ArithmeticException: Non-terminating decimal expansion; no exact representable decimal result

To resolve this problem, you can set precision to 2, and set rounding mode to RoundingMode.HALF_UP (precision and rounding mode depends on your requirement)

  private BigDecimal toRate(final BigDecimal amtIn, final BigDecimal amtOut) {
      if (null == amtIn || null == amtOut) {
          return null;
      }
      return amtIn.divide(amtOut, 2, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100));
  }

Reference
[1] http://docs.oracle.com/javase/7/docs/api/java/math/BigDecimal.html
[2] http://elie2201.blogspot.tw/2012/03/bigdecimal-non-terminating-decimal.html

[Oracle to SQL Server Migration] Outer Join

In Oracle, we use outer join, (+), in the SQL statement as bellows:

SELECT A.MGE_TYPE,
       B.ORG_TYPE,
       F_DBM_FIND_CODE_NM('ORGTP', B.ORG_TYPE) AS ORG_NAME,
       (A.IN_AMT/A.OUT_AMT) * 100 AS RATE,
       (C.IN_AMT/C.OUT_AMT) * 100 AS RATE1_YEAR,
       (D.IN_AMT/D.OUT_AMT) * 100 AS RATE2_TEAR
FROM DBM034FA A,
     DBM030FA B,

  (SELECT *
   FROM DBM034FA
   WHERE TO_CHAR(STA_DATE,'YYYY') = :year1) C,

  (SELECT *
   FROM DBM034FA
   WHERE TO_CHAR(STA_DATE,'YYYY') = :year2) D
WHERE A.AGE = B.AGE
  AND A.STA_DATE = B.STA_DATE
  AND A.AGE = C.AGE(+)
  AND A.FUND_ID = C.FUND_ID(+)
  AND A.MGE_TYPE=C.MGE_TYPE(+)
  AND A.AGE = D.AGE(+)
  AND A.FUND_ID = D.FUND_ID(+)
  AND A.MGE_TYPE=D.MGE_TYPE(+)
  AND TO_CHAR(A.STA_DATE,'YYYY')=:year

Owing to (+) is the specific approach to do outer join in Oracle, it does not work in SQL Server.
Hence, the foregoing SQL statement should be translated as following:

SELECT A.MGE_TYPE,
       B.ORG_TYPE,
       AP_NTA.F_DBM_FIND_CODE_NM('ORGTP', B.ORG_TYPE) AS ORG_NAME,
       (A.IN_AMT/A.OUT_AMT) * 100 AS RATE,
       (C.IN_AMT/C.OUT_AMT) * 100 AS RATE1_YEAR,
       (D.IN_AMT/D.OUT_AMT) * 100 AS RATE2_TEAR
FROM DBM034FA A
INNER JOIN DBM030FA B ON A.AGE = B.AGE
AND A.STA_DATE = B.STA_DATE
AND CONVERT(VARCHAR(4), A.STA_DATE, 112)=:year
LEFT OUTER JOIN
  (SELECT *
   FROM DBM034FA
   WHERE CONVERT(VARCHAR(4), STA_DATE, 112) = :year1) C ON A.AGE = C.AGE
AND A.FUND_ID = C.FUND_ID
AND A.MGE_TYPE=C.MGE_TYPE
LEFT OUTER JOIN
  (SELECT *
   FROM DBM034FA
   WHERE CONVERT(VARCHAR(4), STA_DATE, 112) = :year2) D ON A.AGE = D.AGE
AND A.FUND_ID = D.FUND_ID
AND A.MGE_TYPE=D.MGE_TYPE

Reference
[1] https://technet.microsoft.com/zh-tw/library/ms187518(v=sql.105).aspx

2015/08/01

[閱讀筆記] Steve Jobs在想什麼

向外救援，別把重擔獨自攬下。

保持聚焦，別讓商品特色功能浮濫化。保持事情的精簡，這對已經過於複雜的科技世界來說，會是個優點。

聚焦於你擅長的事情上，其他的交給別人全權處理。

Osborne Effect：一家公司如果提早宣布處於開發階段的優質技術，無疑是一種自殺行為。

避免Osborbe Effect：別提前將新的好東西洩漏出去，除非已經準備出廠上市，免得顧客為了等新東西而不再買現有的貨。

最重要的決策不在於你決定做的那些事情，而在於你決定不做的。

研究發現，被使用者退回的產品中，有將近一半沒故障，只是新的使用者搞不清楚怎麼使用而已。

藝術和科技的創作就像是個人的表現力。藝術家不可能透過一場焦點團體討論來創作一幅畫。

很多時候人們不會知道自己想要什麼，除非你秀給他們看。

Steve Jobs就是蘋果的一人焦點團體。

別怕從頭開始。

所謂精簡化，就是把複雜的事情解決掉。

當你在看某個問題時，心想這個東西很簡單，這就代表你根本不了解問題到底有多複雜。

設計只的是功能上的設計，不是形式而已。對Jobs來說，設計就是指產品的功能運轉方式。

把每個人都找進來。設計不只是設計師的工作，工程師、程式設計人員和行銷人員都可以幫忙發想產品要如何運作。

Jobs不會故意想要設計出『好用』的產品，這種『好用性』是從設計過程中慢慢成形的。

一家新創業公司的成功與否，得看他推出的第一個產品而定。

新創業的公司必須一出手就成功，否則就會一敗塗地。如果你挑對產品，你的勝算就大了。

Apple向來以他的生活型態廣告聞名，從不像其他人都是在廣告裡強調機器的速度、功能和特色，反而採取生活型態行銷。

Apple的iPod廣告極為成功，他的表現手法就是讓年輕人盡情享受戴在頭上的音樂，一字不提iPod的硬體容量。

小而美的工作團隊。Jobs不喜歡團隊規模超過百人以上，因為擔心失焦或無法管理。

別相信『唯命是從』的人。爭辯可以激盪出創意，Jobs希望夥伴們質疑他的想法。

找到你對工作的熱情。

使點狠招，把事情辦好。

就算是看似不可能的事情也要堅持下去。Jobs知道再棘手的問題最終都會獲得解決。

創新和你的研發基金有多雄厚沒關係。當年Apple推出Mac時，IBM花的研發基金至少是Apple的一百倍，所以這和錢的多寡無關，和你有什麼人才，你的領導方式，以及你有多用心有關。

權威分析家總是將Apple歸這種公司歸類成擅長product innovation，Dell則之類的則是落實business innovation的企業。在商業史上，最成功的公司都不是production innovation者，而是那些懂得開發出business innovation 的公司。

講到創新，Jobs很喜歡引用畢卡索的名言：好的藝術家抄；偉大的藝術家偷。對此，Jobs有他的補充說法：『所以我們向來對偷取偉大的點子這件事，一點都不覺得可恥。』

創新就是創造力，把一些東西用讀到的方法整合在一起。創意就是將事物連結在一起。

MagSafe電源轉換器，他被設計的可以輕易與電線分離，以免電腦被摔到地上。這是Apple從日本電鍋身上找到的點子，多年來，基於同樣理由，日本電鍋一直配有詞性的電源轉換器，防止小孩辦到電線時滾水灑出來。

不要對顧客視而不見。Cube電腦會慘敗，是因為他是為設計師所製作的，不是為了顧客。

不要刻意想創新，妄想創新系統化。

專注在產品上。產品是凝聚一切的萬有引力。

竊取。要敢厚顏無恥地竊取別人的好點子。

連結。對Jobs來說，所謂創造力就是把一些東西連結在一起。

Apple II電腦之所以熱賣，靠的就是第一套試算表軟體VisiCalc。任天堂之所以成為遊戲市場的主力產品，都是歸功於他的Mario Brothers電玩遊戲。而Mac也是在Adobe開發出文件和印表機專業的標準語言PostScript程式之後，才開始一飛沖天，開啟了桌上排版作業的革命。

電腦即將成為『生活型態』的重要科技，不再只是『工作上』的重要科技。

如果你錯失了機會，更要賣力迎頭趕上。

別擔心技術從哪裡來，重要的是技術的結合。

槓桿運用你的專業技術，絕對不要從零開始。

相信團隊。iPod沒有所謂的始祖，也沒有所謂的『iPod之父』。他從來就不是一個人的成就，成功永遠來自於眾多發明者。

從個人電腦的歷史來看，硬體的成功與否，關鍵往往卡在他能否擁有獨一無二的軟體：電子試算表VisiCalc之於Apple II電腦，版面設計軟體Aldus Pagemaker和桌上排版系統之餘Mac，Halo遊戲之於Xbox

硬體和軟體的一體化，可以造就出更容易管理和掌控的系統。封閉的系統雖然會侷限你的選擇，卻比較穩定可靠；相形之下，開放的系統就顯得比較脆弱和不可靠，這就是自由的代價。

Microsoft這種想讓硬體變得更有相容性的主動精神『Plug and Play』，反倒變成『Plug and Pray』的戲謔之語，因為有太多軟硬體的結合，後果如何很難預料。

2015/07/31

[Oracle to SQL Server Migration] TO_CHAR - Convert Datetime to String

In Oracle

SELECT to_char(MAX(PRT_DATE),'yyyymmdd')
FROM DBM000F1
WHERE FUNC_ID='DBM030R'
  AND RPT_ID='DBM030P'

But SQL Server does not have to_char function, so it need to translate as bellowing:

SELECT CONVERT(VARCHAR(8), MAX(PRT_DATE), 112)
FROM DBM000F1
WHERE FUNC_ID='DBM030R'
  AND RPT_ID='DBM030P'

Reference
[1] http://www.sqlines.com/oracle-to-sql-server/to_char_datetime
[2] https://msdn.microsoft.com/en-us/library/ms187928.aspx

[Oracle to SQL Server Migration] An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Problem
Here is my original SQL statement in Oracle:

SELECT B.MASTER_AGE,
       ORG_TYPE,
       B.AGE,
       SUM(DEBT_AMT) B_AMT,
       SUBSTR(B.AGE_NAME,1,3) AGE_NAME
FROM DBM032FA A,
     DBM030FA B
WHERE A.STA_DATE = B.STA_DATE
  AND TO_CHAR(B.STA_DATE, 'yyyy') = :qryYear + 1911
  AND A.AGE = B.AGE
  AND DEBT_TYPE = 'B'
GROUP BY ORG_TYPE,
         B.MASTER_AGE,
         B.AGE,
         B.AGE_NAME
ORDER BY org_type

Owing to I need to change database to Microsoft SQL Server. Therefore, the foregoing SQL statement will be translated as following:

SELECT B.MASTER_AGE,
       ORG_TYPE,
       B.AGE,
       SUM(DEBT_AMT) B_AMT,
       SUBSTRING(B.AGE_NAME,1,3) AGE_NAME
FROM DBM032FA A,
     DBM030FA B
WHERE A.STA_DATE = B.STA_DATE
  AND CONVERT(VARCHAR(4), MAX(B.STA_DATE), 112) = :qryYear + 1911
  AND A.AGE = B.AGE
  AND DEBT_TYPE = 'B'
GROUP BY ORG_TYPE,
         B.MASTER_AGE,
         B.AGE,
         B.AGE_NAME
ORDER BY org_type

But as I executed this SQL statement in SQL Sever, it throw this exception unexpectedly:
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.(除非彙總置於 HAVING 子句或選取清單所包含的子查詢中，且彙總的資料行為外部參考，否則不得在 WHERE 子句中出現。)

Solution
Move CONVERT(VARCHAR(4), MAX(B.STA_DATE), 112) = :qryYear + 1911 from where clause to having clause:

SELECT B.MASTER_AGE,
       ORG_TYPE,
       B.AGE,
       SUM(DEBT_AMT) B_AMT,
       SUBSTRING(B.AGE_NAME,1,3) AGE_NAME
FROM DBM032FA A,
     DBM030FA B
WHERE A.STA_DATE = B.STA_DATE
  AND A.AGE = B.AGE
  AND DEBT_TYPE = 'B'
GROUP BY ORG_TYPE,
         B.MASTER_AGE,
         B.AGE,
         B.AGE_NAME
HAVING CONVERT(VARCHAR(4), MAX(B.STA_DATE), 112) = :qryYear+ 1911

ORDER BY org_type

Reference
[1] http://blog.xuite.net/a88370.a88370/cjoushua/73695699-SQL+%E5%AD%B8%E7%BF%92%E7%AD%86%E8%A8%98-Having
[2] https://technet.microsoft.com/en-us//library/cc645611(v=sql.105).aspx

2015/07/29

oracle.net.ns.NetException: Got minus one from a read call

Problem
My web application environment is :

Database: Oracle
Application Server: JBoss

As I startup JBoss, it show this error message unexpectedly:

 Caused by: oracle.net.ns.NetException: Got minus one from a read call
  at oracle.net.ns.Packet.receive(Packet.java:311) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]
  at oracle.net.ns.NSProtocol.connect(NSProtocol.java:300) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]
  at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1140) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]
  at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:340) ~[oracle.jdbc-11.2.0.4-java-6.jar:11.2.0.3.0]
  ... 124 common frames omitted

Resolution
This exception result from the number of process exceed the maximum value.

Therefore, you need to alter the number of process in oracle :

ALTER SYSTEM
SET processes=<number> SCOPE=spfile;

Remember to retsart Oracle.

Reference
[1] http://oraclepath.blogspot.tw/2013/10/javasqlsqlexception-got-minus-one-from.html

2015/07/27

[Oracle to SQL Server Migration] How to convert Oracle User-Defined Function to SQL Server

Here is my oracle user-defined function:

create or replace FUNCTION F_DBM_FIND_CODE_NM (PI_KIND_CODE IN VARCHAR2, PI_CODE_NO   IN NUMBER)
   RETURN VARCHAR2
IS
   CODE_NAME NVARCHAR2(50);
   V_KIND_CODE VARCHAR2(5) :=  PI_KIND_CODE;
   V_CODE_NO    NUMBER (10) := PI_CODE_NO;
   V_SQLSTR VARCHAR2 (200) := '';
BEGIN
   V_SQLSTR := 'SELECT CODE_NAME FROM DBM002FB WHERE KIND_CODE = :1 AND CODE_NO = :2';
   EXECUTE IMMEDIATE V_SQLSTR INTO CODE_NAME USING V_KIND_CODE, V_CODE_NO;  
   
   RETURN CODE_NAME;
END F_DBM_FIND_CODE_NM;

But the syntax has big difference in Microsoft SQL Server:

--Transact-SQL Scalar Function Syntax
CREATE FUNCTION [ schema_name. ] function_name( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
        function_body 
        RETURN scalar_expression
    END
[ ; ]

Hence, the oracle user-defined function will be translated as bellows:

CREATE FUNCTION AP_NTA.F_DBM_FIND_CODE_NM 
    (
      @PI_KIND_CODE VARCHAR(5),
      @PI_CODE_NO NUMERIC(10,0)
    )
RETURNS NVARCHAR(50)
BEGIN
RETURN 
(
    SELECT CODE_NAME FROM DBM002FB WHERE KIND_CODE = @PI_KIND_CODE AND CODE_NO = @PI_CODE_NO
)
END
;

Test function in Microsoft SQL Server:

select AP_NTA.F_DBM_FIND_CODE_NM('IK', 47) as codeName;

Reference
[1] http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5009.htm
[2] https://msdn.microsoft.com/en-us/library/ms186755.aspx

2015/07/26

2015/07 Kyoto

茶山站

清水寺

恵文社一乗寺店

京都文化博物館

南禪寺

鴨川納涼床

貴船神社

貴船流水麵線

鞍馬站

2015/07 Osaka

心齋橋筋

Harukas 300 - 展望台

Universal Studio Japan

Total Pageviews