Requirement
Each year has its own report items, ex. 102年度--未滿一年之短期融資, 103年度--未滿一年之短期融資, 102年度--公債及賒借收入, 103年度--公債及賒借收入, etc.
Our requirement is to generate the two report items automatically next year.
Solution
We can use merge statement to accomplish this requirement.
Step1: Find record(s) in FMS405FA based on our defined search criteria
Step2: Result
2.1: If does not find any record, then insert data into FMS405FA
2.2: If found record(s), then skip it.
MERGE INTO FMS405FA T1 USING DUAL ON(T1.ACCYR = (SELECT TRIM(TO_CHAR(EXTRACT(YEAR FROM SYSDATE)-1911, '000')) FROM DUAL) AND T1.RPT_TYPE='FMS434R' AND T1.RPT_NO='0001') WHEN NOT MATCHED THEN INSERT(T1.ACCYR, T1.RPT_TYPE, T1.RPT_NO, T1.RPT_NM) VALUES( (SELECT TRIM(TO_CHAR(EXTRACT(YEAR FROM SYSDATE)-1911, '000')) FROM DUAL), 'FMS434R', '0001', (SELECT TRIM(TO_CHAR(EXTRACT(YEAR FROM SYSDATE)-1911, '000')) FROM DUAL)||'年度--未滿一年之短期融資')
MERGE INTO FMS405FA T1 USING DUAL ON(T1.ACCYR = (SELECT TRIM(TO_CHAR(EXTRACT(YEAR FROM SYSDATE)-1911, '000')) FROM DUAL) AND T1.RPT_TYPE='FMS434R' AND T1.RPT_NO='0002') WHEN NOT MATCHED THEN INSERT(T1.ACCYR, T1.RPT_TYPE, T1.RPT_NO, T1.RPT_NM) VALUES( (SELECT TRIM(TO_CHAR(EXTRACT(YEAR FROM SYSDATE)-1911, '000')) FROM DUAL), 'FMS434R', '0002', (SELECT TRIM(TO_CHAR(EXTRACT(YEAR FROM SYSDATE)-1911, '000')) FROM DUAL)||'年度--公債及賒借收入')
Reference
[1] http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606
No comments:
Post a Comment