Oracle 10g:为表插入缺失日期和其他值 [英] Oracle 10g: Inserting missing dates for table with others values

问题描述

在此问题中，我能够在其中插入日期

In this question, I was able to insert date in

|----------|----------|
|  DT      |  FLAG    |
|----------|----------|
| 2015-MAY |  E       |
| 2015-JUN |  H       |
| 2015-OCT |  E       |
| 2016-FEB |  E       |
|----------|----------|

获取(缺少的月份插入FLAG V)

to get (missing months are inserted with the FLAG V)

|----------|----------|
|  DT      |  FLAG    |
|----------|----------|
| 2015-MAY |  E       |
| 2015-JUN |  H       |
| 2015-JUL |  V       |
| 2015-AUG |  V       |
| 2015-SEP |  V       |
| 2015-OCT |  E       |
| 2015-NOV |  V       |
| 2015-DEC |  V       |
| 2016-JAN |  V       |
| 2016-FEB |  E       |
|----------|----------|

使用以下代码:

CREATE OR REPLACE PROCEDURE FILL_DATE_GAP AS 
BEGIN
    INSERT INTO DUMMY_DATES 
    SELECT to_date(add_months(date '2015-01-01', level - 1), 'yyyy-mm-dd') mth, 
           'V'
    FROM   DUAL 
    connect by level <= 14
    MINUS
    SELECT DT, 
           FLAG
    FROM   DUMMY_DATES;
END FILL_DATE_GAP;

我要对下表执行相同操作:

I would like to do the same for the following table:

|----------|----------|----------|
|  EID     |  DT      |  FLAG    |
|----------|----------|----------|
|  123     | 2015-MAY |  E       |
|  123     | 2015-JUN |  H       |
|  123     | 2015-OCT |  E       |
|  123     | 2016-FEB |  E       |
|----------|----------|----------|

获得:

|----------|----------|----------|
|  EID     |  DT      |  FLAG    |
|----------|----------|----------|
|  123     | 2015-MAY |  E       |
|  123     | 2015-JUN |  H       |
|  123     | 2015-JUL |  V       |
|  123     | 2015-AUG |  V       |
|  123     | 2015-SEP |  V       |
|  123     | 2015-OCT |  E       |
|  123     | 2015-NOV |  V       |
|  123     | 2015-DEC |  V       |
|  123     | 2016-JAN |  V       |
|  123     | 2016-FEB |  E       |
|----------|----------|----------|

问题 有人可以告诉我该怎么做(复制EID)吗?

Question Can someone tell me how to do that (replicating the EID) ?

推荐答案

如果有人感兴趣，请按照以下步骤进行操作:

If anyone interested, got it work as follow:

        FOR employee_rec IN c_employee
        LOOP
            INSERT INTO XE_GRID_OUTPUT
            SELECT  i_employerId, 
                    employee_rec.EMPLOYEEID,
                    to_date(add_months(date '2014-01-01', level - 1), 'YYYY-MM-DD') mth,
                    'V'
            FROM   DUAL 
            CONNECT BY LEVEL <= 14
            MINUS
            SELECT EMPLOYERID, EMPLOYEEID, DECLARATIONPERIOD, FLAG
            FROM XE_GRID_OUTPUT
            WHERE EMPLOYEEID=employee_rec.EMPLOYEEID;
        END LOOP;

选择给定雇主ID的EMPLOYEEID的光标.

The cursor selecting the EMPLOYEEID for a given employerId.

这篇关于Oracle 10g:为表插入缺失日期和其他值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！