Total Pageviews

2015/10/16

[Oracle] Using "WITH AS" to Refactor SQL

As-Is
Assume I have a SQL statement as bellows:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
SELECT :startYear AS account_yr,
       budget_status AS budget_status,
       data_type AS data_type,
       sum(ALLC_AMOUNT) AS bondARest
FROM
  (SELECT lpad(to_char(a.ACCOUNT_YR, 'yyyy')-1911, 3, '0') AS ACCOUNT_YR,
          a.BUDGET_STATUS AS BUDGET_STATUS,
          a.BUDGET_CODE AS BUDGET_CODE,
          a.DEBT_CODE AS DEBT_CODE,
          a.DATA_TYPE AS DATA_TYPE,
          b.DEBT_TYPE AS DEBT_TYPE,
          b.BOND_TYPE AS BOND_TYPE,
          nvl(b.ALLC_AMOUNT, 0) AS ALLC_AMOUNT
   FROM
     (SELECT ACCOUNT_YR,
             BUDGET_CODE,
             DEBT_CODE,
             DATA_TYPE,
             BUDGET_STATUS,
             END_DATE,
             MAX(BUDGET_STATUS) OVER (PARTITION BY ACCOUNT_YR, BUDGET_CODE, DEBT_CODE, DATA_TYPE, SUBSTR(BUDGET_STATUS, 0, 1)) AS maxBudgetStatus
      FROM DBM091FA
      WHERE DATA_TYPE IN ('C',
                          'I')) a,
        DBM091FB b
   WHERE a.ACCOUNT_YR=b.ACCOUNT_YR
     AND a.BUDGET_CODE=b.BUDGET_CODE
     AND a.DEBT_CODE=b.DEBT_CODE
     AND a.DATA_TYPE=b.DATA_TYPE
     AND a.BUDGET_STATUS=b.BUDGET_STATUS
     AND a.BUDGET_STATUS=a.maxBudgetStatus
   ORDER BY a.ACCOUNT_YR,
            a.BUDGET_STATUS,
            a.BUDGET_CODE,
            a.DEBT_CODE)
WHERE budget_code=1
  AND debt_type=1
  AND bond_type=21
  AND account_yr BETWEEN '083' AND :startYear
GROUP BY budget_status,
         data_type
UNION
SELECT account_yr,
       budget_status,
       data_type,
       sum(ALLC_AMOUNT) bondARest
FROM
  (SELECT lpad(to_char(a.ACCOUNT_YR, 'yyyy')-1911, 3, '0') AS ACCOUNT_YR,
          a.BUDGET_STATUS AS BUDGET_STATUS,
          a.BUDGET_CODE AS BUDGET_CODE,
          a.DEBT_CODE AS DEBT_CODE,
          a.DATA_TYPE AS DATA_TYPE,
          b.DEBT_TYPE AS DEBT_TYPE,
          b.BOND_TYPE AS BOND_TYPE,
          nvl(b.ALLC_AMOUNT, 0) AS ALLC_AMOUNT
   FROM
     (SELECT ACCOUNT_YR,
             BUDGET_CODE,
             DEBT_CODE,
             DATA_TYPE,
             BUDGET_STATUS,
             END_DATE,
             MAX(BUDGET_STATUS) OVER (PARTITION BY ACCOUNT_YR, BUDGET_CODE, DEBT_CODE, DATA_TYPE, SUBSTR(BUDGET_STATUS, 0, 1)) AS maxBudgetStatus
      FROM DBM091FA
      WHERE DATA_TYPE IN ('C',
                          'I')) a,
        DBM091FB b
   WHERE a.ACCOUNT_YR=b.ACCOUNT_YR
     AND a.BUDGET_CODE=b.BUDGET_CODE
     AND a.DEBT_CODE=b.DEBT_CODE
     AND a.DATA_TYPE=b.DATA_TYPE
     AND a.BUDGET_STATUS=b.BUDGET_STATUS
     AND a.BUDGET_STATUS=a.maxBudgetStatus
   ORDER BY a.ACCOUNT_YR,
            a.BUDGET_STATUS,
            a.BUDGET_CODE,
            a.DEBT_CODE)
WHERE budget_code=1
  AND debt_type=1
  AND bond_type=21
  AND account_yr > :startYear
GROUP BY account_yr,
         budget_status,
         data_type
ORDER BY account_yr

You may notice the SQL statement which had been highlighted is duplicated.

To-be
You can utilize "with as..." to do SQL refactor.

 The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis.

The foregoing SQL statement will be amended as following:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
with predictedNumber as (
SELECT lpad(to_char(a.ACCOUNT_YR, 'yyyy')-1911, 3, '0') AS ACCOUNT_YR,
       a.BUDGET_STATUS AS BUDGET_STATUS,
       a.BUDGET_CODE AS BUDGET_CODE,
       a.DEBT_CODE AS DEBT_CODE,
       a.DATA_TYPE AS DATA_TYPE,
       b.DEBT_TYPE AS DEBT_TYPE,
       b.BOND_TYPE AS BOND_TYPE,
       nvl(b.ALLC_AMOUNT, 0) AS ALLC_AMOUNT
FROM
  (SELECT ACCOUNT_YR,
          BUDGET_CODE,
          DEBT_CODE,
          DATA_TYPE,
          BUDGET_STATUS,
          END_DATE,
          MAX(BUDGET_STATUS) OVER (PARTITION BY ACCOUNT_YR, BUDGET_CODE, DEBT_CODE, DATA_TYPE, SUBSTR(BUDGET_STATUS, 0, 1)) AS maxBudgetStatus
   FROM DBM091FA
   WHERE DATA_TYPE IN ('C',
                       'I')) a,
     DBM091FB b
WHERE a.ACCOUNT_YR=b.ACCOUNT_YR
  AND a.BUDGET_CODE=b.BUDGET_CODE
  AND a.DEBT_CODE=b.DEBT_CODE
  AND a.DATA_TYPE=b.DATA_TYPE
  AND a.BUDGET_STATUS=b.BUDGET_STATUS
  AND a.BUDGET_STATUS=a.maxBudgetStatus
ORDER BY a.ACCOUNT_YR,
         a.BUDGET_STATUS,
         a.BUDGET_CODE,
         a.DEBT_CODE
)
select :startYear AS account_yr, budget_status AS budget_status, data_type AS data_type, sum(ALLC_AMOUNT) AS bondARest
from
predictedNumber
where budget_code=1 and debt_type=1 and bond_type=21 and account_yr between '083' and :startYear
group by budget_status, data_type

union

select account_yr, budget_status, data_type, sum(ALLC_AMOUNT) bondARest
from
predictedNumber
where budget_code=1 and debt_type=1 and bond_type=21 and account_yr > :startYear
group by account_yr, budget_status, data_type
order by account_yr


Reference
[1] https://oracle-base.com/articles/misc/with-clause
Posted by Albert Kuo at 10:13 AM 0 comments
Labels:

2015/10/14

[Java] String.format

Requirement
If I have multiple string data, each string data include a Chinese character and an amount with string type, ex. 預 and 14.13

 This kind of data should be formatted as bellows:





String format rule is:

  • The first Chinese string character should enclose with left and right parenthesis.
  • The second amount string character has fixed 16 spaces.


 How-To
You can utilize String.format to fulfill this requirement

format

public static String format(String format,
            Object... args)
Returns a formatted string using the specified format string and arguments.The locale always used is the one returned by Locale.getDefault().
Parameters:
format - A format string
args - Arguments referenced by the format specifiers in the format string. If there are more arguments than format specifiers, the extra arguments are ignored. The number of arguments is variable and may be zero. The maximum number of arguments is limited by the maximum dimension of a Java array as defined by The Java™ Virtual Machine Specification. The behaviour on a null argument depends on the conversion.
Returns:
A formatted string
Throws:
IllegalFormatException - If a format string contains an illegal syntax, a format specifier that is incompatible with the given arguments, insufficient arguments given the format string, or other illegal conditions. For specification of all possible formatting errors, see the Details section of the formatter class specification.
NullPointerException - If the format is null
Since:
1.5
See Also:
Formatter

Sample code:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
    public static void main(String[] args) {
        // The first Chinese string character should enclose with left and right parenthesis.
        // The second amount string character has fixed 16 spaces.
        String strFormat = "(%s)%16s";

        System.out.println(String.format(strFormat, "預", "14.13"));
        System.out.println(String.format(strFormat, "決", "14.34"));
        System.out.println(String.format(strFormat, "預", "0.00"));
        System.out.println(String.format(strFormat, "決", "3.25"));
    }

Reference
[1] https://docs.oracle.com/javase/7/docs/api/java/lang/String.html#format(java.lang.String,%20java.lang.Object...)
[2] http://jax-work-archive.blogspot.tw/2015/02/java-stringformat.html
Posted by Albert Kuo at 2:16 PM 0 comments
Labels:

2015/10/12

[Oracle to SQL Server Migration] Outer Join

In Oracle, we use outer join, (+), in the SQL statement as bellows:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SELECT TO_CHAR(A.STA_DATE,'YYYYMMDD') AS STA_DATE,
       A.AGE,
       A.FUND_ID,
       A.MGE_TYPE,
       F_DBM_FIND_LAGE_NM(A.STA_DATE,A.AGE) AGE_NAME,
       F_DBM_FIND_FUND_NM(A.STA_DATE,A.AGE,A.FUND_ID) FUND_NAME,
       (A.IN_AMT/A.OUT_AMT) * 100 AS RATE0,
       (C.IN_AMT/C.OUT_AMT) * 100 AS RATE1,
       (D.IN_AMT/D.OUT_AMT) * 100 AS RATE2,
       (E.IN_AMT/E.OUT_AMT) * 100 AS RATE3
FROM DBM034FA A,

  (SELECT *
   FROM DBM034FA
   WHERE TO_CHAR(STA_DATE,'YYYY')=:accountYr +1910) C,

  (SELECT *
   FROM DBM034FA
   WHERE TO_CHAR(STA_DATE,'YYYY')=:accountYr +1909) D,

  (SELECT *
   FROM DBM034FA
   WHERE TO_CHAR(STA_DATE,'YYYY')=:accountYr +1908) E
WHERE TO_CHAR(A.STA_DATE,'YYYY')=:accountYr +1911
  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 A.AGE = E.AGE(+)
  AND A.FUND_ID = E.FUND_ID(+)
  AND A.MGE_TYPE=E.MGE_TYPE(+)


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:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
select t.STA_DATE as STA_DATE, 
       t.AGE, 
       t.FUND_ID, 
       t.MGE_TYPE, 
       t.AGE_NAME, 
       t.FUND_NAME, 
       t.RATE0, 
       t.RATE1, 
       t.RATE2, 
       t.RATE3
from
(
SELECT CONVERT(VARCHAR(4), A.STA_DATE, 112),
       A.AGE,
       A.FUND_ID,
       A.MGE_TYPE,
       AP_NTA.F_DBM_FIND_LAGE_NM(A.STA_DATE,A.AGE) AGE_NAME,
       AP_NTA.F_DBM_FIND_FUND_NM(A.STA_DATE,A.AGE,A.FUND_ID) FUND_NAME,
       (A.IN_AMT/A.OUT_AMT) * 100 AS RATE0,
       (C.IN_AMT/C.OUT_AMT) * 100 AS RATE1,
       (D.IN_AMT/D.OUT_AMT) * 100 AS RATE2,
       (E.IN_AMT/E.OUT_AMT) * 100 AS RATE3
FROM DBM034FA A

LEFT OUTER JOIN
  (SELECT *
   FROM DBM034FA
   WHERE CONVERT(VARCHAR(4), STA_DATE, 112)=:accountYr +1910) 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)=:accountYr +1909) D
ON
  A.AGE = D.AGE
  AND A.FUND_ID = D.FUND_ID
  AND A.MGE_TYPE=D.MGE_TYPE

LEFT OUTER JOIN
  (SELECT *
   FROM DBM034FA
   WHERE CONVERT(VARCHAR(4), STA_DATE, 112)=:accountYr +1908) E
ON   
  A.AGE = E.AGE
  AND A.FUND_ID = E.FUND_ID
  AND A.MGE_TYPE=E.MGE_TYPE
 ) t
 where t.STA_DATE=:accountYr +1911




Posted by Albert Kuo at 2:14 PM 0 comments
Labels:

2015/10/02

[AngularJS] ng-change and ng-hide application

Requirement
If 資料類別 choose 預算數，then 預算階段 should show all options:

 If 資料類別 choose 還本數 or 付息數, then 預算階段 should show 預算數 and 決算數

 Original HTML code snippet (does not bind any event yet):
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 <!-- original HTML code snippet (does not bind any event yet) -->
 <div class="row">
   <div class="col-sm-12">
     <div class="col-sm-6 ">
       <label class="control-label label-width-4 text-right">資料類別</label> 
       <input type="radio" value="C" data-ng-model="dbm003eFormBean.dataType">預算數
       <input type="radio" value="P" data-ng-model="dbm003eFormBean.dataType">還本數
       <input type="radio" value="I" data-ng-model="dbm003eFormBean.dataType">付息數
     </div>
     <div class="col-sm-6 "></div>
   </div>
 </div>
 
 <div class="row">
   <div class="col-sm-12">
       <div class="col-sm-6 ">
           <label class="control-label label-width-4 text-right">預算階段</label> 
           <input type="radio" value="P0" data-ng-model="dbm003eFormBean.budgetStatus">{{budgetStatusP0Text}}
           <input type="radio" value="P1" data-ng-model="dbm003eFormBean.budgetStatus">{{budgetStatusP1Text}}
           <input type="radio" value="F0" data-ng-model="dbm003eFormBean.budgetStatus">{{budgetStatusF0Text}}
           <input type="radio" value="F1" data-ng-model="dbm003eFormBean.budgetStatus">{{budgetStatusF1Text}}
           <input type="radio" value="F2" data-ng-model="dbm003eFormBean.budgetStatus">{{budgetStatusF2Text}}
       </div>
       
       <div class="col-sm-6 ">
       </div>
   </div>
 </div>

How-To
Bind ng-change event with 資料類別 radio button 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
 <!-- Updated HTML code snippet
 * bind ng-change event with 資料類別 radio button
 -->
 <div class="row">
  <div class="col-sm-12">
    <div class="col-sm-6 ">
      <label class="control-label label-width-4 text-right">資料類別</label> 
      <input type="radio" value="C" data-ng-model="dbm003eFormBean.dataType" 
             data-ng-change="changeDataType('C')">預算數
      <input type="radio" value="P" data-ng-model="dbm003eFormBean.dataType" 
             data-ng-change="changeDataType('P')">還本數
      <input type="radio" value="I" data-ng-model="dbm003eFormBean.dataType" 
             data-ng-change="changeDataType('I')">付息數
    </div>

    <div class="col-sm-6 "></div>
  </div>
</div>

Bind ng-hide with 預算階段  radio button and control radio button text
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
<!-- * bind ng-hide with 預算階段  radio button -->
<div class="row">
  <div class="col-sm-12">
      <div class="col-sm-6 ">
          <label class="control-label label-width-4 text-right">預算階段</label> 
          <input type="radio" value="P0" data-ng-model="dbm003eFormBean.budgetStatus" 
                 data-ng-hide="hideBudgetStatusP0">{{budgetStatusP0Text}}
          <input type="radio" value="P1" data-ng-model="dbm003eFormBean.budgetStatus" 
                 data-ng-hide="hideBudgetStatusP1">{{budgetStatusP1Text}}
          <input type="radio" value="F0" data-ng-model="dbm003eFormBean.budgetStatus" 
                 data-ng-hide="hideBudgetStatusF0">{{budgetStatusF0Text}}
          <input type="radio" value="F1" data-ng-model="dbm003eFormBean.budgetStatus" 
                 data-ng-hide="hideBudgetStatusF1">{{budgetStatusF1Text}}
          <input type="radio" value="F2" data-ng-model="dbm003eFormBean.budgetStatus" 
                 data-ng-hide="hideBudgetStatusF2">{{budgetStatusF2Text}}
      </div>
      
      <div class="col-sm-6 ">
      </div>
  </div>
</div>

JavaScript code snippet 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
<!-- JavaScript code snippet -->
$scope.changeDataType = function(dataType){
    //if 資料類別(dataType) choose '還本數'(P) or 付息數(I)
    if(dataType =='P' || dataType == 'I'){
     //show '預算數' and '決算數' options
        $scope.hideBudgetStatusP0 = true;
        $scope.hideBudgetStatusP1 = false;
        $scope.hideBudgetStatusF0 = false;
        $scope.hideBudgetStatusF1 = true;
        $scope.hideBudgetStatusF2 = true;
        
  //show 預算數 and 決算數 's radio buttons' text
        $scope.budgetStatusP0Text = '';
        $scope.budgetStatusP1Text = '預算數';
        $scope.budgetStatusF0Text = '決算數';
        $scope.budgetStatusF1Text = '';
        $scope.budgetStatusF2Text = '';
        
  //set default option
        $scope.dbm003eFormBean.budgetStatus = 'P1';
    }
 //if 資料類別(dataType) choose '預算數'(C)
 else if(dataType == 'C'){
     //show 預算階段 all options
        $scope.hideBudgetStatusP0 = false;
        $scope.hideBudgetStatusP1 = false;
        $scope.hideBudgetStatusF0 = false;
        $scope.hideBudgetStatusF1 = false;
        $scope.hideBudgetStatusF2 = false;
        
  //show all radio buttons' text
        $scope.budgetStatusP0Text = '預算案數';
        $scope.budgetStatusP1Text = '預算數';
        $scope.budgetStatusF0Text = '決算數';
        $scope.budgetStatusF1Text = '院編決算數';
        $scope.budgetStatusF2Text = '審定決算數';
        
  //set default option
        $scope.dbm003eFormBean.budgetStatus = 'P0';
    }
};


Posted by Albert Kuo at 11:13 AM 0 comments
Labels:

2015/10/01

[閱讀筆記] The Intelligent Investor (Part 2)


  1. Graham 建議，一般人的投資組合，股市比重不要超過75%，債券則至少要投入25%
  2. 債券與利率的關係就像翹翹板一樣，利率調升，債券價格就會下滑。長期的債券投資表現會優於短期債券。 Graham建議，你無法猜測利率的走向，但是若你是買5~10年的中長期債券，可以降低、淡化利率風險
  3. 當你在1900年用投資1美金投資股票，一個人把分配的股利又繼續投資，另外一個人則是把股利全部花掉。到了2000年，其投資的資產一個是16997美金，另外一個人則是198美金
  4. 不要因為股市上漲而開始搶買股票，也不要因為股市下跌而趕著賣股票，最後只會落得買高賣低的窘境
  5. 防禦型投資者的選股四原則：選股內容適度多樣化；公司必須是大型、有前景與有謹慎的財務策略；該公司必須能長期發放股利；購買價格不要超過其平均盈餘的25倍，最近12個月的20倍
  6. 過去的歷史告訴我們，這家公司現在多炙手可熱，未來就會摔得多重
  7. 當股市出現大屠殺的時候（如2000~2002年），你反而應該開心，因為這頭熊會把市場上的風險一一揪出
  8. 投資人選股的第一步，通常是去尋找有前景的公司，接下來去研究其產品銷售狀況，但是，大部分的投資人都忘記去研究其財務報表，以及估計其企業價值
  9. 每天都去星巴克喝一杯咖啡，發現生意很好，星巴克的股票就值得購買嗎？ 投資人常自以為對這家企業很熟悉，就自信滿滿，且疏於做功課、研究這家公司的財務報表與企業價值，絕對不要犯這種錯誤！
  10. 若在1929年9月投資12000元購買S&P 500指數，10年後只剩下7223元。但是，如果是每個月定期定額投入100元購買S&P 500指數，10年後會成長到15571元，這就是dollar-cost averaging的威力
  11. 最理想的dollar-cost average的方式，是將指數型基金(index fund)放入你的portfolio，這樣你就會擁有每個值得投資的股票或債券，你也不需要再去猜測市場的漲跌
  12. 當一家公司願意發行利率高於平均的債券時，其代表著是擁有比較高的違約風險，如high-yield or junk bonds
  13. 不要盲目購買IPO，因為當開始發行前，價格就已經被炒高。從1980到2001的統計，平均來說，IPO的初次發行價格，經過三年後的年平均報酬，與市場平均表現相較之下，還差了23%
  14. 購買IPO並不是個好主意，而且違反Graham的最基本的原則;不論有多少人瘋狂地想要買這檔股票，當你要買這檔股票前，要先衡量這家公司的盈餘，與他目前股價相較之下，是便宜還是昂貴
  15. 對於智慧型投資人來說，IPO並不是initial public offering,而是It's Probably Overpriced
  16. 對於積極型投資人，Graham對其選股的建議是；選擇relatively unpopular large company、選擇價值被低估的股票
  17. 不要買進官司纏身的公司的股票
  18. 積極型或是防禦型的投資者，你只能選其中一種，Graham認為，若採折衷的話，你很有可能會得到失望的結果
  19. Timing is nothing, timing 只對投機者有意義，因為投機者強調短線進出
  20. 假設股票從低點漲到目前高點約漲了50%，從目前的高點跌回原點則只需33%，如果你無法忍受這點就不能稱之為投資人。舉個例子，假設股票目前10元，漲到15元是50%(5/10*100=50)，跌回10元則是33%(5/15=0.33)
  21. Graham建議符合以下條件的人，才可以投資組合100%都是股票：手上現金足夠支撐你的家庭一年、擁有20年投資經驗、從2000年熊市存活下來、從2000年開始的熊市沒有賣過股票、從2000年來的熊市期間買更多的股票
  22. 股票市場有個弔詭的現象，一流的公司，通常會越成功，但市場就越投機，股價波動就越大，二流的公司則相對較為溫和
  23. 投機者的興趣是從市場波動中獲利；投資者的興趣是在適當的價格買入，並長期持有
  24. 一直等待低點買進的機會，反而會喪失投資機會；除非目前市場價位處於非常高檔的狀態，不如在有閒錢的時候，陸續小額買進
  25. 不要因為股價漲了而買股票，也不要因為股價跌了而賣股票
  26. 事實上，我們無法預測股票市場的價格波動，相同的，債券也是如此
  27. 在投資市場，你無法控制市場本日、下週、下個月、年底的績效，你只能控制你所能控制的，包括：交易頻率(the more you trade, the less you keep)、買進成本、合理的期望、能承受的風險、持有時間以及你的行為
  28. 投資不是一個你死我亡的生存遊戲，投資是如何在投資市場中控制好自己的遊戲，不要因為大眾都買進而跟著買進股票，也不要因為大眾急著賣出就跟著賣出股票
  29. 如果你的投資週期可以持續至少25或30年，唯一的投資方式是：每個月定期定額買進。投資標的最好的選擇就是買入指數型基金(total stock-market index fund)，只有在你需要現金的時候才賣出
  30. 投資並不是要打敗大盤績效，而是要能讓賺的錢足夠滿足你的需要




Posted by Albert Kuo at 8:00 AM 0 comments
Labels: ,

[AngularJS] ng-grid column filter

Requirement

 The following grid is implemented by ng-grid.
The value of the second column, 預算階段, is P1/P1/F0/F1/F2.

 According to the specification of this function, the value of the second column should be translated as bellows:

The ng-grid configuration :
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
    $scope.dbm003eGrid1 = {
            data : 'dbm003eGrid1Data',
            selectedItems: $scope.dbm003eGrid1SelectedRow,
            columnDefs : [
               { field : 'accountYr',    displayName : '預算年度',   width: 100,  cellClass: 'text-left', cellFilter : 'mingGuoYearFilter'},
               { field : 'budgetStatus', displayName : '預算階段',     width: 100, cellClass: 'text-left'},
               { field : 'budgetCode', displayName : 'budgetCode', width: 0,   cellClass: 'text-left', visible : false},
               { field : 'budgetName', displayName : '預算別',     width: 200, cellClass: 'text-left'},
               { field : 'debtCode',   displayName : 'debtCode',   width: 0,    cellClass: 'text-left', visible : false},
               { field : 'debtName',   displayName : '債務別',     width: 400,  cellClass: 'text-left'},
               { field : 'budgetYearPeriod',  displayName : '預算年度',   width: 100,  cellClass: 'text-left'},
               { field : 'beginYear',   displayName : 'beginYear',   width: 0,    cellClass: 'text-left', visible : false},
               { field : 'endYear',     displayName : 'endYear',   width: 0,    cellClass: 'text-left', visible : false},
               { field : 'ttlAmount',   displayName : '預算總額',   width: 150,  cellClass: 'text-right', cellFilter: 'number'}],
               enableColumnResize :true,
               plugins : [ new ngGridFlexibleHeightPlugin({
                  minHeight : 30,
                  maxHeight : 150
               }) ],
               i18n:'zh-tw',
               multiSelect : false
    };


How-To
We can use column filter to fulfill this requirement.
We can create a formatter to convert bugetStatus from code to description
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 /* 轉換預算階段 
     * P0-預算案數
     * P1-預算數
     * F0-決算數
     * F1-院編決算數
     * F2-審定決算數
     */
    formatters.filter('budgetStatusFilter', function(){
    return function (budgetStatus){
        if(budgetStatus == 'P0'){
            return '預算案數';
        }else if(budgetStatus == 'P1'){
            return '預算數';
        }else if(budgetStatus == 'F0'){
            return '決算數';
        }else if(budgetStatus == 'F1'){
            return '院編決算數';
        }else if(budgetStatus == 'F2'){
            return '審定決算數';
        }
          
        };
    });

Apply this filter to bugetStatus column in dbm003eGrid1:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
 $scope.dbm003eGrid1 = {
            data : 'dbm003eGrid1Data',
            selectedItems: $scope.dbm003eGrid1SelectedRow,
            columnDefs : [
               { field : 'accountYr',    displayName : '預算年度',   width: 100,  cellClass: 'text-left', cellFilter : 'mingGuoYearFilter'},
               { field : 'budgetStatus', displayName : '預算階段',     width: 100, cellClass: 'text-left', cellFilter : 'budgetStatusFilter'},
               { field : 'budgetCode', displayName : 'budgetCode', width: 0,   cellClass: 'text-left', visible : false},
               { field : 'budgetName', displayName : '預算別',     width: 200, cellClass: 'text-left'},
               { field : 'debtCode',   displayName : 'debtCode',   width: 0,    cellClass: 'text-left', visible : false},
               { field : 'debtName',   displayName : '債務別',     width: 400,  cellClass: 'text-left'},
               { field : 'budgetYearPeriod',  displayName : '預算年度',   width: 100,  cellClass: 'text-left'},
               { field : 'beginYear',   displayName : 'beginYear',   width: 0,    cellClass: 'text-left', visible : false},
               { field : 'endYear',     displayName : 'endYear',   width: 0,    cellClass: 'text-left', visible : false},
               { field : 'ttlAmount',   displayName : '預算總額',   width: 150,  cellClass: 'text-right', cellFilter: 'number'}],
               enableColumnResize :true,
               plugins : [ new ngGridFlexibleHeightPlugin({
                  minHeight : 30,
                  maxHeight : 150
               }) ],
               i18n:'zh-tw',
               multiSelect : false
    };

Test result



Posted by Albert Kuo at 7:00 AM 0 comments
Labels:

2015/09/30

2015/09 Travel

嘉義市立棒球場

樟湖生態國中小學


Posted by Albert Kuo at 8:00 AM 0 comments
Labels:

2015/09/21

[Oracle] How to get the last day of the month in Oracle

Requirement
If we would like to get the date of the last date of this month, does oracle have any build-in function?

 How-To
Oracle has LAST_DAY function. The last day of the month is defined by the session parameter NLS_CALENDAR. The return type is always DATE, regardless of the datatype of date.

 Example.
1
2
3
   SELECT to_char(sysdate, 'YYYYMMDD') "today",
          to_char(LAST_DAY(SYSDATE), 'YYYYMMDD') " last day of the month"
   FROM DUAL

Result:



Reference
[1] https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions077.htm
Posted by Albert Kuo at 11:37 AM 0 comments
Labels:
Subscribe to: Posts (Atom)