2014/02 Travel

自由廣場

Oracle: how to do insert if data doesn't exist

Here is my database schema:

Requirement
If we cannot find data via ROC_YM, primary key, in FMS415FA, then it should insert specific ROC_YM data into this table

How to do to fulfill this requirement

INSERT INTO FMS451FA(ROC_YM)
SELECT :ROC_YM
FROM dual
WHERE NOT EXISTS
    (SELECT ROC_YM
     FROM FMS451FA
     WHERE ROC_YM = :ROC_YM)

Oracle: how to UPSERT (Update or Insert)

Assume we have a requirement to read text file which sent by external system, and the UPSERT operation either updates or inserts a row in a table, depending if the table already has a row that matches the data.

Oracle 9i introduced the MERGE statement. The MERGE statement takes a list of records which are usually in a staging table, and adds them to a master table. If the record exists in the master table, it should be updated with the new values in the staging table, otherwise insert the record from the staging table.

MERGE statement doesn't always need "multiple tables", but it does need a query as the source. Something like this should work:

MERGE INTO FMS420FB T1 USING DUAL ON (T1.YEAR = :YEAR
                                      AND T1.MONTH= :MONTH
                                      AND T1.SUBJECT_ID=:SUBJECT_ID) WHEN MATCHED THEN
UPDATE
SET T1.SUBJECT=:SUBJECT,
               T1.AMT=:AMT WHEN NOT MATCHED THEN
INSERT (T1.YEAR,
        T1.MONTH,
        T1.SUBJECT_ID,
        T1.SUBJECT,
        T1.AMT)
VALUES(:YEAR,
       :MONTH,
       :SUBJECT_ID,
       :SUBJECT,
       :AMT)

Reference
[1] http://www.oracle-base.com/articles/10g/merge-enhancements-10g.php
[2] http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9016.htm#SQLRF55128

Create Oracle Function

Here is a SQL statement:

SELECT ROC_YM
FROM FMS451FA

The result is 

Owning to client's requirement, we need to add slash between Chinese year and month. Therefore, the SQL statement will adjust as bellows:

SELECT SUBSTR(ROC_YM, 1, 3)||'/'||SUBSTR(ROC_YM, 4, 2) AS ROC_YM
FROM FMS451FA

The result will be converted into:

   

Because it is a common requirement, and apply to all functions. So we can create an Oracle function to increase reusability.

Here is the syntax to create Oracle function

Hence, we create an Oracle function to add slash between Chinese year and month

CREATE OR REPLACE FUNCTION FORMAT_YYYMM(INPUT VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN SUBSTR(INPUT, 1, 3)||'/'||SUBSTR(INPUT, 4, 2); END;

Whenever we encounter this date formatting requirement, we can reuse this function as bellows:

SELECT FORMAT_YYYMM(ROC_YM)
FROM FMS451FA

Reference

[1] http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_5009.htm

[2] http://www.techonthenet.com/oracle/functions.php

Online SQL Formatting Tool

In some situations, we may need to figure out the report bugs via extracting SQL statement from console or log file. But the SQL statement normally does not have any formatting, and hard to read. It may lead to more debug time to spend. 

Here is a helpful SQL formatting tool: http://sqlformat.appspot.com/

Copy your SQL statement into this text area

Click Format SQL button or use Ctrl+F hotkey

The formatting SQL statement will show in the button of page

常用JavaScript function整理

 //取得今年民國年  
 function getCurrentYear(){  
    var date = new Date();  
    return date.getFullYear() - 1911;    
 }  
 //取得當下月份  
 function getCurrentMonth(){  
   var date = new Date();  
   return date.getMonth()+1;  
 }  
 //取得當下日期  
 function getCurrentDate(){  
   var date = new Date();  
   return date.getDate();  
 }  
 //取得當月的第一天(民國年+月+日)  
 function getFirstDateOfCurrentMonth(){  
   var date = new Date();  
   var currentYear = date.getFullYear() - 1911;  
   var currentMonth = date.getMonth()+1;  
   return leftPad(String(currentYear), 3) +  
       leftPad(String(currentMonth), 2)+  
       leftPad('1', 2);  
 }  
 //取得當天日期  
 function getCurrentDate(){  
   var date = new Date();  
   var currentYear = date.getFullYear() - 1911;  
   var currentMonth = date.getMonth()+1;  
   var currentDate = date.getDate();  
   return leftPad(String(currentYear), 3) +  
       leftPad(String(currentMonth), 2)+  
       leftPad(currentDate, 2);  
 }  
 //取得當月最後一天  
 function getEndDayInMonth(year, month){  
   return XDate.getDaysInMonth(year, parseInt(month)-1);  
 }  
 //減去指定年，如20141104減去2年，變成20121104  
 function minusYears(date, year){  
   return date.addYears(0 - parseInt(year), true).toString('yyyyMMdd');  
 }  
 //減去指定月，如20141104減去2個月，變成20140904  
 function minusMonths(date, month){  
   return date.addMonths(0 - parseInt(month), true).toString('yyyyMMdd');  
 }  
 //確認是否為數字  
 function isNumber(n) {  
    return !isNaN(parseFloat(n)) && isFinite(n);  
 }  
 //檢查是串是否不為空值且不為undefined  
 function isNotEmptyOrUndefined(str){  
  return str != '' && !angular.isUndefined(str) && str != null;    
 }  
 //檢查是否為空值且為undefined  
 function isEmptyOrUndefined(str){  
  return str == '' || angular.isUndefined(str) || str == null;    
 }  
 //檢查日期起迄，如起始日期為1021101，結束日期為1021103，其會回傳true;  
 //如起始日期為1021103，結束入其為1021101，其會回傳false  
 function isValidStartAndEndDate(startDate, endDate){  
   var isValid = false;  
   var startYear = parseInt(startDate.substring(0, 3))+1911;  
   var startMonth = parseInt(startDate.substring(3, 5)-1);  
   var startDay  = parseInt(startDate.substring(5, 7));  
   var endYear  = parseInt(endDate.substring(0, 3))+1911;  
   var endMonth  = parseInt(endDate.substring(3, 5)-1);  
   var endDay   = parseInt(endDate.substring(5, 7));  
   var sXdate = new XDate(startYear, startMonth, startDay);  
   var eXdate = new XDate(endYear, endMonth, endDay);  
   var diffDays = sXdate.diffDays(eXdate);  
   if(diffDays < 0){  
     isValid = false;  
   }else{  
     isValid = true;  
   }  
   return isValid;  
 }  
 //檢查日期起迄，如起始日期為1020101，結束日期為1020131，其會回傳true;  
 //如起始日期為1031103，結束入其為1021101，其會回傳false  
 function isValidStartAndEndYYYMM(startDate, endDate){  
   var isValid = false;  
   var startYear = parseInt(startDate.substring(0, 3))+1911;  
   var startMonth = parseInt(startDate.substring(3, 5)-1);  
   var startDay  = parseInt(startDate.substring(5, 7));  
   var endYear  = parseInt(endDate.substring(0, 3))+1911;  
   var endMonth  = parseInt(endDate.substring(3, 5)-1);  
   var endDay   = parseInt(endDate.substring(5, 7));  
   var sXdate = new XDate(startYear, startMonth, startDay);  
   var eXdate = new XDate(endYear, endMonth, endDay);  
   var diffDays = sXdate.diffMonths(eXdate);  
   if(diffDays < 0){  
     isValid = false;  
   }else{  
     isValid = true;  
   }  
   return isValid;  
 }  
 //西元轉民國，如20141104轉1021104  
 function convertFromWesternToChinse(western){  
   var year = parseInt(western.substring(0, 4))-1911;  
   var monthDate = western.substring(4, western.length);  
   return leftPad(String(year), 3)+monthDate;  
 }  
 //若未滿位，左邊補0  
 function leftPad(val, length) {  
   var str = '' + val;  
   while (str.length < length) {  
     str = '0' + str;  
   }  
   return str;  
 }  

註: 有用到xdate字眼的部分，要到 http://arshaw.com/xdate/ 這裡下載js檔

Oracle MERGE statement

If you would like to select rows from one or more sources for update or insertion into a table, you can make good use of MERGE statement. 
You can specify conditions to determine whether to update or insert into the target table.
Semantics

• INTO Clause
• your target table
• USING Clause
• your data source
• ON Clause
• set your conditions in on clause
• merge_update_clause
• specifies the new column values of the target table
• merge_insert_clause
•  specifies values to insert into the column of the target table if the condition of the ON clause is false

Here is a practical example:

 MERGE INTO FMS451FA T1  
 USING(  
  SELECT SUBSTR(ROC_YM,1,3) AS ROC_YR   
     ,SUBSTR(ROC_YM,4,2) AS ROC_MM  
     ,CUM_INCM_TAX --當月收入(2)-稅課收入   
     ,CUM_NON_INCM_TAX --當月收入(2)-非稅課收入(含其他)   
     ,CUM_INCM_LOAN --當月收入(2)-融資財源   
     ,CUM_TTL_INCM --當月收入(2)-合計   
     ,CUM_TTL_PAY_AMT --當月支出(3)   
     FROM FMS406VE  
 ) T2  
 ON (T1.ROC_YM = T2.ROC_YR||T2.ROC_MM)  
 WHEN MATCHED THEN UPDATE SET T1.CUM_INCM_TAX = T2.CUM_INCM_TAX, --稅課收入  
                T1.CUM_NON_INCM_TAX = T2.CUM_NON_INCM_TAX,--非稅課收入(含其他)  
                T1.CUM_INCM_LOAN = T2.CUM_INCM_LOAN,--融資財源  
                T1.CUM_TTL_INCM = T2.CUM_TTL_INCM,--合計   
                T1.CUM_TTL_PAY_AMT = T2.CUM_TTL_PAY_AMT--當月支出(3)   
 WHEN NOT MATCHED THEN INSERT (T1.ROC_YM,   
                T1.CUM_INCM_TAX,   
                T1.CUM_NON_INCM_TAX,   
                T1.CUM_INCM_LOAN,   
                T1.CUM_TTL_INCM,   
                T1.CUM_TTL_PAY_AMT)   
 VALUES(T2.ROC_YR||T2.ROC_MM,   
     T2.CUM_INCM_TAX,   
     T2.CUM_NON_INCM_TAX,   
     T2.CUM_INCM_LOAN,   
     T2.CUM_TTL_INCM,   
     T2.CUM_TTL_PAY_AMT);  

2014 春節旅遊

嘉義-九華山地藏庵

光影旅行者《陳澄波百二互動展》嘉義展 (原來陳澄波也是228受難者)

日本皇居對照

嘉義市史蹟資料館

嘉義市植物園

隙頂二延平步道

How to use UNPIVOT to Convert Columns into Rows

Here is our original data structure:

We would like to turn columns into rows:

Here is our original SQL statement:

 SELECT ROC_YM,   
       CUM_INCM_TAX,   --當月收入(2)-稅課收入  
       CUM_NON_INCM_TAX, --當月收入(2)-非稅課收入(含其他)  
       CUM_INCM_LOAN,  --當月收入(2)-融資財源  
       CUM_TTL_INCM,   --當月收入(2)-合計  
       CUM_TTL_PAY_AMT  --當月支出(3)  
 FROM FMS406VE   
 WHERE roc_ym BETWEEN 09701 AND 10212  
 ORDER BY ROC_YM;  

If we would like to turn columns to rows, we need to utilize UNPIVOT as bellows:

 SELECT ROC_YM,   
       AMOUNT_TYPE,   
       AMOUNT  
 FROM FMS406VE   
 UNPIVOT INCLUDE NULLS(AMOUNT FOR AMOUNT_TYPE IN  
 　　　　　　　　　　　　(CUM_INCM_TAX AS '01',   --當月收入(2)-稅課收入  
           　          CUM_NON_INCM_TAX AS '02', --當月收入(2)-非稅課收入(含其他)  
                       CUM_INCM_LOAN AS '03',  --當月收入(2)-融資財源  
                       CUM_TTL_INCM AS '04',   --當月收入(2)-合計  
                       CUM_TTL_PAY_AMT AS '05')) --當月支出(3)  
 WHERE ROC_YM BETWEEN 09701 AND 10212 --年月區間  
 ORDER BY ROC_YM, AMOUNT_TYPE; 

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: