Total Pageviews

2014/11/20

[Oracle] Customize ADD_DAYS function in ROC era system

Requirement
I am using ROC era system as my date format (ex. 1031120 represents 20141120).
If I would like to create an Oracle function to do add days just like the Oracle build-in function ADD_MONTHS.

Solution
Here is my tailor-made Oracle function.
ADD_DAYS returns the date date plus integer days. 

  • The MINGUO_YYYMMDD argument only accept ROC era system as  date format, ex. 1031120.
  • The integer argument, DAYS, can be an integer or any value that can be implicitly converted to an integer. 
  • The return type is always VARCHAR2 with  ROC era system as  date format.
  • If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date.


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
create or replace FUNCTION ADD_DAYS 
(
  MINGUO_YYYMMDD IN VARCHAR2 --CHINESE YEAR
, DAYS IN NUMBER --5 MEANS ADD 5 DAYS, -3 MEANS SUBSTRACT 3 DAYS
) 
RETURN VARCHAR2 IS NEW_MINGUO_YYY_MM_DD VARCHAR2(7); --RETURN RESULT
BEGIN
  SELECT NEW_MINGUO_YYY_MM_DD 
  INTO NEW_MINGUO_YYY_MM_DD
  FROM(
    SELECT SUBSTR(TO_CHAR(TO_DATE(SUBSTR(MINGUO_YYYMMDD, 0, 3)+1911 || SUBSTR(MINGUO_YYYMMDD, 4, 4), 'YYYYMMDD')+DAYS, 'YYYYMMDD'), 0, 4)-1911 ||
            SUBSTR(TO_CHAR(TO_DATE(SUBSTR(MINGUO_YYYMMDD, 0, 3)+1911 || SUBSTR(MINGUO_YYYMMDD, 4, 4), 'YYYYMMDD')+DAYS, 'YYYYMMDD'), 5, 4) AS NEW_MINGUO_YYY_MM_DD
    FROM DUAL
  );
  RETURN NEW_MINGUO_YYY_MM_DD;
END ADD_DAYS;

Test
Add 5 days

1
SELECT ADD_DAYS('1031120', 5) FROM DUAL;



Subtract 3 days
1
SELECT ADD_DAYS('1030803', -3) FROM DUAL;







2014/11/18

[Oracle] How to add serial number for each record

Requirement
Here is my SQL statement to do query:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT YYY_MM,
       ASP,
       ITEM_CD,
       ITEM_NM,
       AMT_A,
       AMT_B,
       AMT_C
FROM FMS420FA
WHERE YYY_MM='10210'
ORDER BY ASP

Query result:

If I would like to add serial number for each record as bellowing, how to do it?


Solution
We can make good use of ROW_NUMBER function. ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1.


The SQL statement had been revised as following:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
SELECT ROW_NUMBER() OVER (PARTITION BY YYY_MM
                          ORDER BY ASP) SEQ,
                                        YYY_MM,
                                        ASP,
                                        ITEM_CD,
                                        ITEM_NM,
                                        AMT_A,
                                        AMT_B,
                                        AMT_C
FROM FMS420FA
WHERE YYY_MM='10210'



Reference
[1] https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm

An internal error occurred during: "Initializing Java Tooling". Duplicate field name&signature in class file org/eclipse/jdt/internal/compiler/ast/ASTNode

Problem
I change to a brand new workspace to import project from gitlab.
After imported and try to do maven => update projects, it show "An internal error occurred during: "Initializing Java Tooling". Duplicate field name&signature in class file org/eclipse/jdt/internal/compiler/ast/ASTNode". 

Here has its screenshot:


Root Cause
This error results from lombok's bug. 


Solution
Step1. Shut down your eclipse
Step2. Go to here to download the jar file : http://projectlombok.org/download-edge.html
Step3. follow the instructions in  http://projectlombok.org/download-edge.html
Step4. Startup your eclipse and do again.



Reference
[1] http://stackoverflow.com/questions/21931107/eclipse-wont-switch-workspace

2014/11/13

How to Convert Oracle rows to columns

Requirement
Here has my query SQL statement.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
SELECT SUBSTR(PDATE, 0, 5) AS YYY_MM,
       ROUND(SUM(NVL(PAY_T_BILL, 0))/100000000, 0) AS PAY_T_BILL
FROM FMS406VD
WHERE SUBSTR(PDATE, 0, 5) IN ('10201',
                              '10202',
                              '10203',
                              '10204',
                              '10205',
                              '10206',
                              '10207',
                              '10208',
                              '10209',
                              '10210',
                              '10211',
                              '10212')
  AND BL_ROC_YM = SUBSTR(PDATE, 0, 5)
GROUP BY SUBSTR(PDATE, 0, 5)
ORDER BY SUBSTR(PDATE, 0, 5)


Here is the query result

If I would like to convert row into column, how to do it?

Solution
You can make good use of PIVOT to fulfill this requirement. The SQL statement can be re-written into this way

 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
SELECT AMT1, AMT2, AMT3, AMT4, AMT5, AMT6, AMT7, AMT8, AMT9, AMT10, AMT11, AMT12
          FROM(
          SELECT SUBSTR(PDATE, 0, 5) AS YYY_MM,
                 ROUND(SUM(NVL(PAY_T_BILL, 0))/100000000, 0) AS PAY_T_BILL
          FROM FMS406VD   
          WHERE SUBSTR(PDATE, 0, 5) IN ('10201', 
                                        '10202', 
                                        '10203', 
                                        '10204', 
                                        '10205', 
                                        '10206', 
                                        '10207', 
                                        '10208', 
                                        '10209', 
                                        '10210', 
                                        '10211',  
                                        '10212' )
                AND BL_ROC_YM = SUBSTR(PDATE, 0, 5)
          GROUP BY SUBSTR(PDATE, 0, 5)          
          ORDER BY SUBSTR(PDATE, 0, 5)
          )
          PIVOT(SUM(PAY_T_BILL) FOR YYY_MM IN (
                                          '10201' AS AMT1, 
                                          '10202' AS AMT2, 
                                          '10203' AS AMT3,
                                          '10204' AS AMT4,
                                          '10205' AS AMT5,
                                          '10206' AS AMT6,
                                          '10207' AS AMT7,
                                          '10208' AS AMT8,
                                          '10209' AS AMT9,
                                          '10210' AS AMT10,
                                          '10211' AS AMT11,
                                          '10212' AS AMT12))

The query result is as bellowing:


Reference
[1] http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html

2014/11/12

What's the difference between 'commit' and 'commit and push' in Git?

When I want to commit changes into git repository, I see two options to commit. One is "Commit", another one is "Commit and Push".

Here has a clear picture
If you do git commit, it means you only commit changes into your local repository.
If you do git push, it means you will commit changes to remote repository.


Demo
If I click Commit

Remote repository does not know what happened.



 If I click "Commit and Push"

Remote repository received these changes.

Reference
[1] http://stackoverflow.com/questions/2745076/what-are-the-differences-between-git-commit-and-git-push

2014/11/11

How to create project in GitLab and import project into Eclipse

Go to GitLab to create my project
1. Click New Project
 2. Fill in your project name, namespace => Create project


Open Git Bash
1. Change directory to my specific dirctory => make dbm directory => Initialized empty Git repository
1:  albert@ALBERT-PC ~  
2:  $ cd d:  
3:  albert@ALBERT-PC /d  
4:  $ cd git  
5:  albert@ALBERT-PC /d/git  
6:  $ mkdir dbm  
7:  albert@ALBERT-PC /d/git  
8:  $ cd dbm  
9:  albert@ALBERT-PC /d/git/dbm  
10:  $ git init  
11:  Initialized empty Git repository in d:/git/dbm/.git/  

2. create README file => add README to track => commit
1:  albert@ALBERT-PC /d/git/dbm (master)  
2:  $ touch README  
3:  albert@ALBERT-PC /d/git/dbm (master)  
4:  $ git add README  
5:  albert@ALBERT-PC /d/git/dbm (master)  
6:  $ git commit -m "first commit"  
7:  [master (root-commit) b45a8a4] first commit  
8:   1 file changed, 0 insertions(+), 0 deletions(-)  
9:   create mode 100644 README  

3. add git remote repository => push to remote
1:  albert@ALBERT-PC /d/git/dbm (master)  
2:  $ git remote add origin http://192.168.31.166/ifmis/dbm.git  
3:  albert@ALBERT-PC /d/git/dbm (master)  
4:  $ git push -u origin master  
5:  Username for 'http://192.168.31.166': albert_kuo  
6:  Password for 'http://albert_kuo@192.168.31.166':  
7:  Counting objects: 3, done.  
8:  Writing objects: 100% (3/3), 206 bytes | 0 bytes/s, done.  
9:  Total 3 (delta 0), reused 0 (delta 0)  
10:  To http://192.168.31.166/ifmis/dbm.git  
11:   * [new branch]   master -> master  
12:  Branch master set up to track remote branch master from origin.  

README file had been commit into git

Assume I create my project skeleton into my git folder

Add all files into track => commit => push to remote
1:  albert@ALBERT-PC /d/git/dbm (master)  
2:  $ git add .  
3:  warning: LF will be replaced by CRLF in dbm-webapp/src/main/jasperreports/README.md.  
4:  The file will have its original line endings in your working directory.  
5:  warning: LF will be replaced by CRLF in dbm-webapp/src/main/webapp/WEB-INF/templates/README.md.  
6:  The file will have its original line endings in your working directory.  
7:  warning: LF will be replaced by CRLF in dbm-webapp/src/main/webapp/scripts/README.md.  
8:  The file will have its original line endings in your working directory.  
9:  warning: LF will be replaced by CRLF in dbm-webapp/src/main/webapp/styles/README.md.  
10:  The file will have its original line endings in your working directory.  
11:  warning: LF will be replaced by CRLF in dbm-webapp/src/test/database/README.md.  
12:  The file will have its original line endings in your working directory.  
13:  warning: LF will be replaced by CRLF in dbm-webapp/src/test/database/test-datasource.xml.template-or  
14:  acle.  
15:  The file will have its original line endings in your working directory.  
16:  warning: LF will be replaced by CRLF in dbm-webapp/src/test/database/test-datasource.xml.template-sq  
17:  lserver.  
18:  The file will have its original line endings in your working directory.  
19:  warning: LF will be replaced by CRLF in pom.xml.  
20:  The file will have its original line endings in your working directory.  
21:  albert@ALBERT-PC /d/git/dbm (master)  
22:  $ git commit -m "create project skeleton"  
23:  [master 4abf50e] create project skeleton  
24:  warning: LF will be replaced by CRLF in dbm-webapp/src/main/jasperreports/README.md.  
25:  The file will have its original line endings in your working directory.  
26:  warning: LF will be replaced by CRLF in dbm-webapp/src/main/webapp/WEB-INF/templates/README.md.  
27:  The file will have its original line endings in your working directory.  
28:  warning: LF will be replaced by CRLF in dbm-webapp/src/main/webapp/scripts/README.md.  
29:  The file will have its original line endings in your working directory.  
30:  warning: LF will be replaced by CRLF in dbm-webapp/src/main/webapp/styles/README.md.  
31:  The file will have its original line endings in your working directory.  
32:  warning: LF will be replaced by CRLF in dbm-webapp/src/test/database/README.md.  
33:  The file will have its original line endings in your working directory.  
34:  warning: LF will be replaced by CRLF in dbm-webapp/src/test/database/test-datasource.xml.template-or  
35:  acle.  
36:  The file will have its original line endings in your working directory.  
37:  warning: LF will be replaced by CRLF in dbm-webapp/src/test/database/test-datasource.xml.template-sq  
38:  lserver.  
39:  The file will have its original line endings in your working directory.  
40:  warning: LF will be replaced by CRLF in pom.xml.  
41:  The file will have its original line endings in your working directory.  
42:   22 files changed, 498 insertions(+)  
43:   create mode 100644 README.md  
44:   create mode 100644 dbm-entity/pom.xml  
45:   create mode 100644 dbm-entity/src/main/java/gov/nta/entity/package-info.java  
46:   create mode 100644 dbm-service/pom.xml  
47:   create mode 100644 dbm-service/src/main/java/gov/nta/dbm/repository/package-info.java  
48:   create mode 100644 dbm-service/src/main/java/gov/nta/dbm/service/package-info.java  
49:   create mode 100644 dbm-webapp/pom.xml  
50:   create mode 100644 dbm-webapp/src/main/jasperreports/README.md  
51:   create mode 100644 dbm-webapp/src/main/java/gov/nta/dbm/web/controller/package-info.java  
52:   create mode 100644 dbm-webapp/src/main/java/gov/nta/dbm/web/dto/package-info.java  
53:   create mode 100644 dbm-webapp/src/main/java/gov/nta/dbm/web/rest/package-info.java  
54:   create mode 100644 dbm-webapp/src/main/resources/gov/nta/dbm/Messages.properties  
55:   create mode 100644 dbm-webapp/src/main/webapp/WEB-INF/jboss-deployment-structure.xml  
56:   create mode 100644 dbm-webapp/src/main/webapp/WEB-INF/jboss-web.xml  
57:   create mode 100644 dbm-webapp/src/main/webapp/WEB-INF/templates/README.md  
58:   create mode 100644 dbm-webapp/src/main/webapp/WEB-INF/web.xml  
59:   create mode 100644 dbm-webapp/src/main/webapp/scripts/README.md  
60:   create mode 100644 dbm-webapp/src/main/webapp/styles/README.md  
61:   create mode 100644 dbm-webapp/src/test/database/README.md  
62:   create mode 100644 dbm-webapp/src/test/database/test-datasource.xml.template-oracle  
63:   create mode 100644 dbm-webapp/src/test/database/test-datasource.xml.template-sqlserver  
64:   create mode 100644 pom.xml  
65:  albert@ALBERT-PC /d/git/dbm (master)  
66:  $ git push -u origin master  
67:  Username for 'http://192.168.31.166': albert_kuo  
68:  Password for 'http://albert_kuo@192.168.31.166':  
69:  Counting objects: 64, done.  
70:  Delta compression using up to 4 threads.  
71:  Compressing objects: 100% (34/34), done.  
72:  Writing objects: 100% (63/63), 10.12 KiB | 0 bytes/s, done.  
73:  Total 63 (delta 2), reused 0 (delta 0)  
74:  To http://192.168.31.166/ifmis/dbm.git  
75:    b45a8a4..4abf50e master -> master  
76:  Branch master set up to track remote branch master from origin.

See..my project skeleton had been commit

Import Git project into Eclipse
1. Copy url

2. File => Import => choose SCM type to "git" => paste url => Finish

3. Well done.




Do not have git connector when I want to Checkout Maven project from SCM

Problem
I would like to import Maven project from Git
File => import

Check out Maven Projects from SCM => Next

I do not have "git" option to select !

Solution
Install git connector

Window => Preferences

Maven => Discovery => Open Catalog

Check "m2e-egit" => Finish

After installation, click Yes to restart eclipse

File => import => Check out Maven Projects from SCM => Next
We have "git" option now!




Unable to ignore resources. Attempted to beginRule: .....

Problem
After I imported Maven project from Git, I always get this error when I do maven-->update projects

Solution
This link, http://www.eclipse.org/forums/index.php/t/493493/ , provides some workaround solutions. 

But you can check the post in the bottom, the author say its plug-in's problem. After I updated it, this error message had gone away.



2014/11/09

Failed to launch Eclipse after updated Mac OS Yosemite (10.10)

Problem
After I upgraded Mac OS Yosemite, I found out I cannot launch Eclipse correctly.

Solution
Please go to here to download Java for OS X 2014-001
http://support.apple.com/kb/DL1572?viewlocale=en_US&locale=en_US

After installed Java for OS X 2014-001, my Eclipse can startup correctly.