Oracle Analytic functions

The general syntax of analytic function is:
Function(arg1,..., argn) OVER ( [PARTITION BY <...>] [ORDER BY <....>] [] )

•  The OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results
• PARTITION BY divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.
• ORDER BY clause Defines the logical order of the rows within each partition of the result set. 

According to this SQL statement:

1:  select FYR, --會計年度  
2:        ROC_YM, --年月  
3:        INCM_LOAN --融資財源收入  
4:  from FMS406VC   
5:  where fyr=102 and ROC_YM=10201;  

We can get this result, and we would like to accumulate the value of INCM_LOAD:

We can use partition by clause to accumulate the value of INCM_LOAD

1:  select FYR, --會計年度  
2:        ROC_YM, --年月  
3:        SUM(SUM(INCM_LOAN)) OVER (PARTITION BY FYR ORDER BY ROC_YM) AS CUM_INCM_LOAD --累積融資財源收入  
4:  from FMS406VC   
5:  where fyr=102 and ROC_YM=10201  
6:  GROUP BY FYR ,ROC_YM;  

And we can get the expected result:

How to convert year from Western to Chinese in Oracle? (在Oracle中如何將西元年轉民國年)

Requirement
Assume we had a table which contains all work days, and recorded in CALENDAR_DATE column with date data type.

According customers' requirement, they would to show Chinese year instead of Western year.

Solution
We can make good use of EXTRACT function which provide by Oracle.

SQL statement:

1:  SELECT CALENDAR_DATE,   
2:      TRIM(TO_CHAR(EXTRACT(YEAR FROM CALENDAR_DATE)-1911, '000'))||  
3:      TRIM(TO_CHAR(EXTRACT(MONTH FROM CALENDAR_DATE), '00'))||  
4:      TRIM(TO_CHAR(EXTRACT(DAY FROM CALENDAR_DATE), '00'))   
5:      AS CHINESE_CALENDAR  
6:   FROM SYS111FC  
7:  WHERE NO_WORK_IND = '0' AND ROC_YR='102'  

After executing the SQL as above, we can see 2013 had been convert to 102.

Quick way to build prototypes - Moqups

Official site - https://moqups.com/home/

Moqups is a nifty HTML5 App used to create wireframes, mockups or UI concepts, prototypes depending on how you like to call them.

You can use Moqups to build prototype quickly to confirm user requirement as bellowing:

2013-14 日本八天七夜

橫濱夜景

東京鐵塔

原宿街頭

皇居

吾妻橋夜景

東京天空樹

東京車站夜景

台場夜景

淺草寺

六本木夜景

六本木大蜘蛛

Solution to ORA-00911(invalid character, 字元無效)

Problem
As I would like to execute an insert SQL statement, it throws ORA-00911 error. After googling, it recommend to "remove the invalid character from the statement or enclose the object name in double quotation marks". 
But the insert SQL statement is working fine in SQL Developer, only failed in java program.

Solution
Remove the semi-colon in the end of SQL statement.

Cannot Import Java Source Code via Astah Pro

Problem
When I used Astah Pro to do import java code

But it show the error which complain encoding issues:


Solution
As we do import java code, we need to set the property of "Charset of imported files" to UTF-8.

Then we can import Java source code successfully.

An internal error occurred during: "Initializing Java Tooling" from Eclipse

Problem
One day as I startup my eclipse it show the error as bellows:

Solution

Step1. Close Eclipse

Step2. Go to your eclipse workspace, and go to this directory

 %workspace%/.metadata/.plugins/org.eclipse.jdt.core/  

ex.

Step3. delete variablesAndContainers.dat

Step4. Start Eclipse

How to pad a string with spaces when size of data is unknown in Microsoft SQL Server

Requirement
When I select a character data with varchar(6) from Microsoft SQL Server....

And these data should have fixed length in report.
If the data is 01, it should right pad with 4 spaces.
If the data is 01234, it should right pad with 1 space.

Solution
If we would like to meet this requirement in SQL Server, we can use 'CAST' function which provided by SQL Server

select cast(repno as char(6)) from NSS313FA  

Utilizing cast function in SQL statement, you can get fixed length in report no matter how long the data is.

   

You can get further information in http://technet.microsoft.com/en-us/library/aa226054(v=sql.80).aspx

How to display column footer immediately without any spaces after details band

Problem
This is my report design in iReport:

Here is the Detail property and Column Footer property setting:

But...as I generate pdf, there has unnecessary spaces between Detail band and Column Footer band.

Solution

Step1. Right click --> property

Step2. Check the "Float column footer" property

Then this problem will be solved. There will not have any spaces between Detail band and Column Footer band.

Why I can't print dashed lines?

Problem
This is my setting in iReport. The line had been set as dashed line.

And you can see the dashed line in chrome pdf viewer

But as I print this pdf file, I cannot see the dashed line!

Root Cause
It may result from some problem in Chrome PDF Viewer.

Solution
Go to chrome://plugins/
 Disable Chrome PDF Viewer and Enable Adboe Reader

Then click the print button.

See...the dashed line had shown in my paper

Why can't I save PDF in Chrome?

Problem
My chrome version is up-to-date.

But why I can't save PDF after viewing it within Chrome, the save button does not work.


Root Cause
It may result from some problem in Chrome PDF Viewer.

Solution
Go to chrome://plugins/
 Disable Chrome PDF Viewer and Enable Adboe Reader

Then the save button is working now!

How to convert the amount from Arabic numerals to Chinese number (如何將阿拉伯數字金額轉大寫國字)

Requirement
In our payment slip, we need to convert the amount from Arabic numerals to Chinese number. ex. 325250 will be converted to 參拾貳萬伍仟貳佰伍拾.

Solution
Here has a utility method to do conversion.

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

/** * Convert to Chinese amount * * @param amount * the amount * @return the string */ public static String convertToChineseAmt(Long amount) { String num[] = { "零", "壹", "貳", "參", "肆", "伍", "陸", "柒", "捌", "玖" }; String xx[] = { "拾", "佰", "仟" }; String yy[] = { "萬", "億", "兆", "京" }; String chineseAmt = ""; int i; int temp; boolean first = true; boolean zero = false; for (i = 0; (int) (amount / (long) Math.pow(10, i)) != 0; i++) { ;// 計算位數i } if (i == 0) { return num[0]; } for (int k = (i - 1) / 4; k >= 0; k--) { temp = (int) ((amount / (long) Math.pow(10, 4 * k)) % (int) Math.pow(10, 4)); if (temp == 0) { zero = true; continue; } for (int x = 3; x >= 0; x--) { if (first) { x = (i - 1) % 4; first = false; } if (temp / (int) Math.pow(10, x) % 10 == 0) { zero = true; } else { if (zero) { chineseAmt += num[0]; zero = false; } chineseAmt += num[temp / (int) Math.pow(10, x) % 10]; if (x > 0) { chineseAmt += xx[x - 1]; } } } if (k > 0) { chineseAmt = chineseAmt + yy[k - 1]; } } return chineseAmt; }

Test
We inputted three amount into this method to do conversion

1 2 3 4 5

public static void main(String args[]) { System.out.println(ReportUtils.convertToChineseAmt(2500L)); System.out.println(ReportUtils.convertToChineseAmt(50100L)); System.out.println(ReportUtils.convertToChineseAmt(325250L)); }

The result is as bellows:

1 2 3

貳仟伍佰 伍萬零壹佰 參拾貳萬伍仟貳佰伍拾

How to clear an array in AngularJS?

Question
If we have an array in AngularJS, how do I clear its content?

Answer
If we have an array, ex.

 $scope.itemarray = ['A', 'B', 'C'];  

If we would like to clear its content in specific situation, what we need to do is as bellowing:

 $scope.itemarray.length = 0;  

How to do collection filter in Java

Requirement
Assume I have a collection of Cta612pDto, and this collection contains:

1:  [Cta612pDto [acc=0100, amt=13033356, seqnm=(*)年度預算支出, ypay=null, formattedAmt=null],   
2:  Cta612pDto [acc=0101, amt=3809733, seqnm=  一般政務支出                                , ypay=null, formattedAmt=null],   
3:  Cta612pDto [acc=0102, amt=3894280, seqnm=  國防支出                                  , ypay=null, formattedAmt=null],   
4:  Cta612pDto [acc=0103, amt=2532563, seqnm=  教育科學文化支出                              , ypay=null, formattedAmt=null],   
5:  Cta612pDto [acc=0104, amt=1655232, seqnm=  經濟發展支出                                , ypay=null, formattedAmt=null],   
6:  Cta612pDto [acc=0105, amt=460708, seqnm=  社會福利支出                                , ypay=null, formattedAmt=null],   
7:  Cta612pDto [acc=0106, amt=59111, seqnm=  社區發展及環境保護支出                           , ypay=null, formattedAmt=null],   
8:  Cta612pDto [acc=0107, amt=366285, seqnm=  退休撫卹支出                                , ypay=null, formattedAmt=null],   
9:  Cta612pDto [acc=0109, amt=255444, seqnm=  一般補助及其他支出                             , ypay=null, formattedAmt=null],   
10:  Cta612pDto [acc=0200, amt=130015, seqnm=(*)以前年度支出                                 , ypay=null, formattedAmt=null],   
11:  Cta612pDto [acc=0400, amt=14271298, seqnm=(*)特種基金及保管款支出                             , ypay=null, formattedAmt=null],   
12:  Cta612pDto [acc=0500, amt=0, seqnm=(*)國庫券及短期借款還本支出, ypay=null, formattedAmt=null],   
13:  Cta612pDto [acc=8888, amt=1539861, seqnm=null, ypay=null, formattedAmt=null],   
14:  Cta612pDto [acc=9800, amt=757028109, seqnm=合計, ypay=null, formattedAmt=null],   
15:  Cta612pDto [acc=9900, amt=849981138506, seqnm=累計, ypay=null, formattedAmt=null],   
16:  Cta612pDto [acc=9991, amt=1539861, seqnm=  支付註銷, ypay=null, formattedAmt=null],   
17:  Cta612pDto [acc=9992, amt=728053579, seqnm=  支出收回, ypay=null, formattedAmt=null],   
18:  Cta612pDto [acc=9999, amt=728053579, seqnm=null, ypay=null, formattedAmt=null]]  

We need to remove some java beans from this collection when acc is 8888 or 9999.

Solution
We can make good use of PredicateUtils.nonePredicate to define our conditions which provide by Apache commons Collection. It will create a new Predicate that returns true if none of the specified predicates are true. If the array of predicates is empty, then this predicate returns true. 
And also use CollectionUtils.filter to filter the collection by applying a Predicate to each element.

1:      BeanPropertyValueEqualsPredicate acc8888Predicate = new BeanPropertyValueEqualsPredicate(  
2:          "acc", "8888");  
3:      BeanPropertyValueEqualsPredicate acc9999Predicate = new BeanPropertyValueEqualsPredicate(  
4:          "acc", "9999");  
5:      Predicate predicate = PredicateUtils.nonePredicate(new Predicate[] { acc8888Predicate,  
6:          acc9999Predicate });  
7:      CollectionUtils.filter(dataList, predicate);  

After doing CollectionUtils.filter, you can find out two java beans had been removed from data list collection.

1:  [Cta612pDto [acc=0100, amt=13033356, seqnm=(*)年度預算支出, ypay=null, formattedAmt=13,033,356.00],   
2:  Cta612pDto [acc=0101, amt=3809733, seqnm=  一般政務支出                                , ypay=null, formattedAmt=3,809,733.00　　　],   
3:  Cta612pDto [acc=0102, amt=3894280, seqnm=  國防支出                                  , ypay=null, formattedAmt=3,894,280.00　　　],   
4:  Cta612pDto [acc=0103, amt=2532563, seqnm=  教育科學文化支出                              , ypay=null, formattedAmt=2,532,563.00　　　],   
5:  Cta612pDto [acc=0104, amt=1655232, seqnm=  經濟發展支出                                , ypay=null, formattedAmt=1,655,232.00　　　],   
6:  Cta612pDto [acc=0105, amt=460708, seqnm=  社會福利支出                                , ypay=null, formattedAmt=460,708.00　　　],   
7:  Cta612pDto [acc=0106, amt=59111, seqnm=  社區發展及環境保護支出                           , ypay=null, formattedAmt=59,111.00　　　],   
8:  Cta612pDto [acc=0107, amt=366285, seqnm=  退休撫卹支出                                , ypay=null, formattedAmt=366,285.00　　　],   
9:  Cta612pDto [acc=0109, amt=255444, seqnm=  一般補助及其他支出                             , ypay=null, formattedAmt=255,444.00　　　],   
10:  Cta612pDto [acc=0200, amt=130015, seqnm=(*)以前年度支出                                 , ypay=null, formattedAmt=130,015.00],   
11:  Cta612pDto [acc=0400, amt=14271298, seqnm=(*)特種基金及保管款支出                             , ypay=null, formattedAmt=14,271,298.00],   
12:  Cta612pDto [acc=0500, amt=0, seqnm=(*)國庫券及短期借款還本支出, ypay=null, formattedAmt=0.00],   
13:  Cta612pDto [acc=9800, amt=757028109, seqnm=合計, ypay=null, formattedAmt=757,028,109.00],   
14:  Cta612pDto [acc=9900, amt=849981138506, seqnm=累計, ypay=null, formattedAmt=849,981,138,506.00],   
15:  Cta612pDto [acc=9991, amt=1539861, seqnm=  支付註銷, ypay=null, formattedAmt=1,539,861.00　　　],   
16:  Cta612pDto [acc=9992, amt=728053579, seqnm=  支出收回, ypay=null, formattedAmt=728,053,579.00　　　]]  

Why iReport eat my spaces

Requirement

We had a report requirement as bellows:

You can see some items' amount need to do indent. Others don't.

Problem

Here we add 7 single-byte spaces for some items' amount to do indent based on specific conditions.

1:   DecimalFormat df = new DecimalFormat();  
2:      df.setMaximumFractionDigits(2);  
3:      df.setMinimumFractionDigits(2);  
4:      for (Cta612pDto vo : dataList) {  
5:        if (StringUtils.startsWith(vo.getSeqnm(), "(*)") || "9800".equals(vo.getAcc())  
6:            || "9900".equals(vo.getAcc())) {  
7:          vo.setFormattedAmt(df.format(vo.getAmt()));  
8:        } else {  
9:          vo.setFormattedAmt(df.format(vo.getAmt()) + "       ");  
10:        }  
11:        formattedList.add(vo);  
12:      }  

But it does not work:

Root cause

 The iReport seem trim your spaces automatically. Therefore, it does not work for this circumstance.

Solution
We need to use double-byte spaces(全形空白) instead of single-byte spaces(半形空白).

1:   DecimalFormat df = new DecimalFormat();  
2:      df.setMaximumFractionDigits(2);  
3:      df.setMinimumFractionDigits(2);  
4:      for (Cta612pDto vo : dataList) {  
5:        if (StringUtils.startsWith(vo.getSeqnm(), "(*)") || "9800".equals(vo.getAcc())  
6:            || "9900".equals(vo.getAcc())) {  
7:          vo.setFormattedAmt(df.format(vo.getAmt()));  
8:        } else {  
9:          vo.setFormattedAmt(df.format(vo.getAmt()) + "　　　");  
10:        }  
11:        formattedList.add(vo);  
12:      }