Total Pageviews

2014/09/07

Warren Buffett's 23 Most Brilliant Insights About Investing

挺不錯的投資建議
  1. Buying a stock is about more than just the price. 
  2. You don't have to be a genius to invest well.
  3. But, master the basics. 
  4. Don't buy a stock just because everyone hates it. 
  5. Bad things aren't obvious when times are good.
  6. Always be liquid.
  7. The best time to buy a company is when it's in trouble. 
  8. Stocks have always come out of crises. 
  9. Don't be fooled by that Cinderella feeling you get from great returns.
  10. Think long-term. 
  11. Forever is a good holding period. 
  12. Buy businesses that can be run by idiots. 
  13. Be greedy when others are fearful.
  14. You don't have to move at every opportunity.
  15. Ignore politics and macroeconomics when picking stocks.
  16. The more you trade, the more you underperform. 
  17. Price and value are not the same. 
  18. There are no bonus points for complicated investments. 
  19. A good businessperson makes a good investor.
  20. Higher taxes aren't a dealbreaker.
  21. Companies that don't change can be great investments.
  22. Time will tell.
  23. This is the most important thing. "Rule No. 1: never lose money; rule No. 2: don't forget rule No. 1"

發現最近看的幾本書,漸漸歸納出一些原則
  • 要正確預測未來股價、營收成長是很難的,所以股價長跌勢不可預測的,這就是統計學家或經濟學家所提出的隨機漫步(random walk)
  • 投資的原則越簡單越好
  • 股價不代表其真實價值,運用本益比來協助判斷
  • 不要購買股價高過其價值的股票
  • How to Value a Business, and How to Think About Market Prices是兩個最基本且重要的課題
  • 長期持有的報酬率,遠大於一直殺進殺出的投資人
  • 找個笨蛋都能會經營的產業
  • 汰弱留強,好的股票繼續持有,不好的股票就賣掉
  • 長期來說,股市報酬率會是正數
  • 危機入市
  • 不要跟著群眾一窩蜂,在人們恐懼時貪婪,在人們貪婪時恐懼

http://www.businessinsider.com/warren-buffetts-investing-quotes-2014-8?op=1


2014/09/06

A good web site to learn AngularJS


If we would like to learn AngularJS, you can go to here, http://jsbin.com/, to learn. You just need a web browser, do not need to have any IDE(Integrated Development Environment) tools.

Here has a quick start.
1. Go to http://jsbin.com/

2. Click "Add Library"


3. Choose "Angular 1.2.14 stable"


 4. It will import "angular.min.js" automatically

5. Place "ng-app" directive in html tag 

6. create a input text for typing name

7. Check output preivew

8. Design the welcome message as user type in name

9. Demo



2014/09/05

SQL Query Order of Operations

Problem
Here is the select SQL statement:
SELECT YEAR,
       PERIOD,
       NVL(YEAR_PERIOD, ' ') AS YEAR_PERIOD, --年度+期別
       START_DATE, --日期
       NVL(AMT1, 0) AS AMT1, --舉借數額 -國庫券(1)
       NVL(AMT2, 0) AS AMT2,  --舉借數額 -短期借款(2)
       NVL(AMT3, 0) AS AMT3,  --償還數額-國庫券(3)
       NVL(AMT4, 0) AS AMT4 --償還數額-短期借款(4)
FROM FMS435FB
WHERE START_DATE BETWEEN :LAST_YEAR||'1201' AND :LAST_YEAR||'1231'
ORDER BY START_DATE DESC , PERIOD DESC, YEAR_PERIOD DESC
And I would to get the first record from the result set.

Therefore, I add rownum=1 in my where clause. 
Owning to I need to do union with another select statement, so I move it into subquery as bellows:
SELECT *
FROM
  (SELECT YEAR,
          PERIOD,
          NVL(YEAR_PERIOD, ' ') AS YEAR_PERIOD, --年度+期別
          START_DATE,  --日期
          NVL(AMT1, 0) AS AMT1, --舉借數額 -國庫券(1)
          NVL(AMT2, 0) AS AMT2, --舉借數額 -短期借款(2)
          NVL(AMT3, 0) AS AMT3, --償還數額-國庫券(3)
          NVL(AMT4, 0) AS AMT4 --償還數額-短期借款(4)
   FROM FMS435FB
   WHERE START_DATE BETWEEN :LAST_YEAR||'1201' AND :LAST_YEAR||'1231' AND ROWNUM=1
   ORDER BY START_DATE DESC , PERIOD DESC, YEAR_PERIOD DESC)
UNION ALL .....
But I get this record!

Root Cause
This unexpected result result from the SQL query order of operations, the order is:
FROM clause --> WHERE clause --> GROUP BY clause --> HAVING clause --> SELECT clause -->ORDER BY clause

If we marked rownum=1 and order by cluase
SELECT *
FROM
  (SELECT YEAR,
          PERIOD,
          NVL(YEAR_PERIOD, ' ') AS YEAR_PERIOD, --年度+期別
          START_DATE, --日期
          NVL(AMT1, 0) AS AMT1, --舉借數額 -國庫券(1)
          NVL(AMT2, 0) AS AMT2, --舉借數額 -短期借款(2)
          NVL(AMT3, 0) AS AMT3, --償還數額-國庫券(3)
          NVL(AMT4, 0) AS AMT4 --償還數額-短期借款(4)
   FROM FMS435FB
   WHERE START_DATE BETWEEN :LAST_YEAR||'1201' AND :LAST_YEAR||'1231'-- AND ROWNUM=1
 --ORDER BY START_DATE DESC , PERIOD DESC, YEAR_PERIOD DESC
 )
UNION ALL .....
We can find the first record is '1021226' not '1021227', if we marked rownum=1 and order by cluase. Owing the query order operation is from --> where --> select --> order by, 'rownum=1' have already get the first record before order by. So it's the reason why we get the unexpected result.

Solution
We need to get the result which had already been sorted, and apply rownum=1 to get the first record.
SELECT *
FROM
  (SELECT YEAR,
          PERIOD,
          NVL(YEAR_PERIOD, ' ') AS YEAR_PERIOD, --年度+期別
          START_DATE, --日期
          NVL(AMT1, 0) AS AMT1, --舉借數額 -國庫券(1)
          NVL(AMT2, 0) AS AMT2, --舉借數額 -短期借款(2)
          NVL(AMT3, 0) AS AMT3, --償還數額-國庫券(3)
          NVL(AMT4, 0) AS AMT4 --償還數額-短期借款(4)
   FROM FMS435FB
   WHERE START_DATE BETWEEN :LAST_YEAR||'1201' AND :LAST_YEAR||'1231'
   ORDER BY START_DATE DESC , PERIOD DESC, YEAR_PERIOD DESC)
WHERE ROWNUM=1
UNION ALL .....


Reference
[1] http://www.bennadel.com/blog/70-sql-query-order-of-operations.htm

2014/08/28

[Oracle] How to Create Before Insert Trigger

Requirement
Here has one of my table schema, and the first column, SEQ_NO, will be assigned serial number which value will be get from SE_FMS_CF0006 sequence.

I hope I do not need get the sequence value manually and assign to SEQ_NO column manually.

Column
Type
Size
Nulls
Default
Comments
SEQ_NO
number
20
流水號SELECT SE_FMS_CF0006.NEXTVAL FROM DUAL
AGE
varchar2
7
 √ 
null
資料提供機構代碼
FILE_TYPE
varchar2
8
 √ 
null
傳輸檔案識別
PDATE
varchar2
7
資料日期
FYR
varchar2
3
 √ 
null
會計年度
ACC
varchar2
11
科目代碼
ACC_APPENDIX
varchar2
3
科目代碼附加碼
AMOUNT
number
15,2
 √ 
0
金額
UPDATE_DATE
timestamp(6)
11,6
 √ 
now
更新日期
USER_ID
varchar2
20
 √ 
null
異動者


Solution
We can make good use of trigger to fulfill this requirement, you can check the before insert trigger. 
As you do insert, you can skip SEQ_NO, this trigger will get the serial number before you do insert.
CREATE OR REPLACE TRIGGER AP_PSR.TR_BI_CF0006  
  BEFORE INSERT ON AP_PSR.CF0006  FOR EACH ROW  
 BEGIN  
  IF :NEW.SEQ_NO IS NULL THEN  
   SELECT SE_FMS_CF0006.NEXTVAL INTO :NEW.SEQ_NO FROM DUAL;  
  END IF;  
 END;  

Demo
Assume I would like to execute the insert statement, I only provide values to mandatory columns except SEQ_NO.

INSERT INTO cf0006(pdate, acc, acc_appendix)
VALUES('1030401', '123', '123');
Then execute the select statement:
SELECT * FROM cf0006;

See.... I did not assign any value to SEQ_NO, its value assigned by TR_BI_CF0006  trigger automatically.


Reference
[1] http://www.techonthenet.com/oracle/triggers/before_insert.php

2014/08/27

ORA-17004 : Invalid Column Type

Problem
I am using JPA(Java Persistence API) as my persistence tier technology to write data into Oracle database. Here is the database schema for the targeted table:
Column
Type
Size
Nulls
Default
Comments
EXE_ID
number
20
執行ID, Calendar.getInstance().getTimeInMillis()
FUNCTION_ID
varchar2
20
功能代號
MESSAGE
nvarchar2
2000
 V 
null
執行訊息
EXECUTE_TYPE
varchar2
1
V
null
C:CRON JOB, M: MANNUAL, U:Upload
USER_ID
varchar2
20
 V 
null
批次執行時,帶功能名稱,手動執行時,帶執行人USER_ID
UPDATE_DATE_TIME
timestamp(6)
11,6
 V 
now
執行時間
FILE_NAME
varchar2
40
 V 
null
檔案名稱

Here is the code snippet:
1:    public void writeLog(Boolean isSuccessful, String funId, String exceptionMsg) {  
2:      try {  
3:        StringBuilder sql = new StringBuilder();  
4:        sql.append("INSERT INTO FMS900FA(EXE_ID, FUNCTION_ID, MESSAGE, EXECUTE_TYPE, USER_ID, FILE_NAME)");  
5:        sql.append(" VALUES(:EXE_ID, :FUNCTION_ID, :MESSAGE, :EXECUTE_TYPE, :USER_ID, :FILE_NAME)");  
6:        Map<String, Object> params = new HashMap<String, Object>();  
7:        params.put("EXE_ID", BigInteger.valueOf(Calendar.getInstance().getTimeInMillis()));  
8:        params.put("FUNCTION_ID", funId);  
9:        if (isSuccessful) {  
10:          params.put("MESSAGE", "執行成功");  
11:        } else {  
12:          params.put("MESSAGE", exceptionMsg);  
13:        }  
14:        params.put("EXECUTE_TYPE", "C");  
15:        params.put("USER_ID", "ADMIN");  
16:        params.put("FILE_NAME", "NA");  
17:        sqlExecutor.insert(sql, params);  
18:      } catch(Exception e) {  
19:        throw e;  
20:      }  
21:    }   

But program throw SQLException
1:  Caused by: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO FMS900FA(EXE_ID, FUNCTION_ID, MESSAGE, EXECUTE_TYPE, USER_ID, FILE_NAME)  VALUES(?, ?, ?, ?, ?, ?) ]; SQL state [99999]; error code [17004]; invalid column type; nested exception is java.sql.SQLException: invalid column type  
2:       at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
3:       at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
4:       at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
5:       at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:660) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
6:       at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:909) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
7:       at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:933) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
8:       at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:313) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
9:       at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:318) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
10:       at com.cht.commons.persistence.query.SqlExecutor.execute(SqlExecutor.java:97) ~[cht-commons-persistence-0.1.0-SNAPSHOT.jar!/:0.1.0-SNAPSHOT]  
11:       at com.cht.commons.persistence.query.SqlExecutor.insert(SqlExecutor.java:157) ~[cht-commons-persistence-0.1.0-SNAPSHOT.jar!/:0.1.0-SNAPSHOT]  
12:       at com.cht.commons.persistence.query.SqlExecutor.insert(SqlExecutor.java:146) ~[cht-commons-persistence-0.1.0-SNAPSHOT.jar!/:0.1.0-SNAPSHOT]  
13:       at gov.nta.fms.service.FmsBatchLog.writeLog(FmsBatchLog.java:139) ~[fms-service-1.0.0-SNAPSHOT.jar:1.0.0-SNAPSHOT]  
14:       at gov.nta.fms.service.Fms435xService.initFms435fa(Fms435xService.java:228) ~[fms-service-1.0.0-SNAPSHOT.jar:1.0.0-SNAPSHOT]  
15:       at gov.nta.fms.service.Fms435xService$$FastClassBySpringCGLIB$$75f6a95.invoke() ~[na:na]  
16:       at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
17:       at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:711) ~[spring-aop-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
18:       at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
19:       at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:98) ~[spring-tx-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
20:       at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:262) ~[spring-tx-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
21:       at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:95) ~[spring-tx-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
22:       at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
23:       at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:58) ~[spring-aop-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
24:       ... 111 common frames omitted  
25:  Caused by: java.sql.SQLException: invalid column type  
26:       at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8761) ~[na:na]  
27:       at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8259) ~[na:na]  
28:       at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9012) ~[na:na]  
29:       at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8993) ~[na:na]  
30:       at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:230) ~[na:na]  
31:       at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.setObject(WrappedPreparedStatement.java:986) ~[na:na]  
32:       at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:402) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
33:       at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:235) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
34:       at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:150) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
35:       at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.setValues(PreparedStatementCreatorFactory.java:300) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
36:       at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:252) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
37:       at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:638) ~[spring-jdbc-4.0.3.RELEASE.jar!/:4.0.3.RELEASE]  
38:       ... 129 common frames omitted  

How to solve it?
This SQLException, ORA-17004 : Invalid Column Type, results from EXE_ID this column.

We use BigInteger originally, but fail to do insert. As we change to use BigDecimal, it's working fine now. But the root cause is still unknown.
1:    public void writeLog(Boolean isSuccessful, String funId, String exceptionMsg) {  
2:      try {  
3:        StringBuilder sql = new StringBuilder();  
4:        sql.append("INSERT INTO FMS900FA(EXE_ID, FUNCTION_ID, MESSAGE, EXECUTE_TYPE, USER_ID, FILE_NAME)");  
5:        sql.append(" VALUES(:EXE_ID, :FUNCTION_ID, :MESSAGE, :EXECUTE_TYPE, :USER_ID, :FILE_NAME)");  
6:        Map<String, Object> params = new HashMap<String, Object>();  
7:        params.put("EXE_ID", new BigDecimal(Calendar.getInstance().getTimeInMillis()));  
8:        params.put("FUNCTION_ID", funId);  
9:        if (isSuccessful) {  
10:          params.put("MESSAGE", "執行成功");  
11:        } else {  
12:          params.put("MESSAGE", exceptionMsg);  
13:        }  
14:        params.put("EXECUTE_TYPE", "C");  
15:        params.put("USER_ID", "ADMIN");  
16:        params.put("FILE_NAME", "NA");  
17:        sqlExecutor.insert(sql, params);  
18:      } catch(Exception e) {  
19:        throw e;  
20:      }  
21:  

2014/08/22

Oracle : DBMS_OUTPUT

Requirement
We would like to print debugging information from stored procedures, packages or triggers

For example.
We have an merge SQL statement to generate initial data and will be executed from 097~104 (Chinese year) by each month before system go life. i.e. passing 09701, 09702, 09703, 09704, 09705, 09706, 09707, 09708, 09709, 09710, 09711, 09712, 09801, .....10412 into merge SQL.

We would like to print the value from while loop in stored procedure to confirm the value is correct or not.

Solution
We can utilize DBMS_OUTPUT.put_line for debugging, ex.
DECLARE sYr number := 97;

 eYr number := 104;

 fyr varchar2(7);

 sMon number := 1;

 mon varchar2(2);


 BEGIN while sYr <= eYr LOOP IF length(sYr) = 2 THEN fyr := '0' || sYr;

 ELSE fyr := sYr;

 END IF;

 while sMon <=12 LOOP IF length(sMon) = 1 THEN mon := '0' || sMon;

 ELSE mon := sMon;

 END IF;

 --ignore merge sql
 DBMS_OUTPUT.put_line('fyr='||fyr||';month='||mon);

COMMIT;

SELECT sMon + 1 INTO sMon FROM dual;

 END LOOP;

 sMon :=1;


SELECT sYr + 1 INTO sYr FROM dual;

 END LOOP;

 END;
The following is the debugging information
1:  fyr=097;month=01  
2:  fyr=097;month=02  
3:  fyr=097;month=03  
4:  fyr=097;month=04  
5:  fyr=097;month=05  
6:  fyr=097;month=06  
7:  fyr=097;month=07  
8:  fyr=097;month=08  
9:  fyr=097;month=09  
10:  fyr=097;month=10  
11:  fyr=097;month=11  
12:  fyr=097;month=12  
13:  fyr=098;month=01  
14:  fyr=098;month=02  
15:  fyr=098;month=03  
16:  fyr=098;month=04  
17:  fyr=098;month=05  
18:  fyr=098;month=06  
19:  fyr=098;month=07  
20:  fyr=098;month=08  
21:  fyr=098;month=09  
22:  fyr=098;month=10  
23:  fyr=098;month=11  
24:  fyr=098;month=12  
25:  fyr=099;month=01  
26:  fyr=099;month=02  
27:  fyr=099;month=03  
28:  fyr=099;month=04  
29:  fyr=099;month=05  
30:  fyr=099;month=06  
31:  fyr=099;month=07  
32:  fyr=099;month=08  
33:  fyr=099;month=09  
34:  fyr=099;month=10  
35:  fyr=099;month=11  
36:  fyr=099;month=12  
37:  fyr=100;month=01  
38:  fyr=100;month=02  
39:  fyr=100;month=03  
40:  fyr=100;month=04  
41:  fyr=100;month=05  
42:  fyr=100;month=06  
43:  fyr=100;month=07  
44:  fyr=100;month=08  
45:  fyr=100;month=09  
46:  fyr=100;month=10  
47:  fyr=100;month=11  
48:  fyr=100;month=12  
49:  fyr=101;month=01  
50:  fyr=101;month=02  
51:  fyr=101;month=03  
52:  fyr=101;month=04  
53:  fyr=101;month=05  
54:  fyr=101;month=06  
55:  fyr=101;month=07  
56:  fyr=101;month=08  
57:  fyr=101;month=09  
58:  fyr=101;month=10  
59:  fyr=101;month=11  
60:  fyr=101;month=12  
61:  fyr=102;month=01  
62:  fyr=102;month=02  
63:  fyr=102;month=03  
64:  fyr=102;month=04  
65:  fyr=102;month=05  
66:  fyr=102;month=06  
67:  fyr=102;month=07  
68:  fyr=102;month=08  
69:  fyr=102;month=09  
70:  fyr=102;month=10  
71:  fyr=102;month=11  
72:  fyr=102;month=12  
73:  fyr=103;month=01  
74:  fyr=103;month=02  
75:  fyr=103;month=03  
76:  fyr=103;month=04  
77:  fyr=103;month=05  
78:  fyr=103;month=06  
79:  fyr=103;month=07  
80:  fyr=103;month=08  
81:  fyr=103;month=09  
82:  fyr=103;month=10  
83:  fyr=103;month=11  
84:  fyr=103;month=12  
85:  fyr=104;month=01  
86:  fyr=104;month=02  
87:  fyr=104;month=03  
88:  fyr=104;month=04  
89:  fyr=104;month=05  
90:  fyr=104;month=06  
91:  fyr=104;month=07  
92:  fyr=104;month=08  
93:  fyr=104;month=09  
94:  fyr=104;month=10  
95:  fyr=104;month=11  
96:  fyr=104;month=12  


Reference
[1] http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_output.htm#i1000105