Inserting duplicate entries into the target table using MERGE-CodePudding

This is the extended version of the question but when I tried inserting one additional column which is jira_key then duplicate entries are getting loaded:

How to make existing values to null from the existing plsql logic

CREATE TABLE test_tab1(seq_id NUMBER(10),
e_id NUMBER(10),
jira_key varchar2(20),
stage_code NUMBER(10));

INSERT INTO test_tab1(1,11,'A',2);
INSERT INTO test_tab1(1,12,'B',3);

CREATE SEQUENCE test_tab2_sq;
CREATE TABLE test_tab2(seq_id NUMBER(10),

e_id NUMBER(10),

jira_key varchar2(20),

stage_code NUMBER(10),
start_date DATE,
end_date DATE);

I need to load the data into the table test_tab2 based on the stage_code column of test_tab1.

Explanation:

If stage_code is 3 in test_tab1 table then there would be 4 entries in the table test_tab2 starting from 0 to 3 and start_date and end_date should be SYSDATE for stage_code < original stage_code and if stage_code = original code then end_date will be NULL. Below expected output.

 -------- ------ ------------- ------------- ---------- 
| SEQ_ID | E_ID | STAGE_CODE  | START_DATE  | END_DATE |
 -------- ------ ------------- ------------- ---------- 
|      1 |   11 |           0 | 21-06-22    | 21-06-22 |
|      2 |   11 |           1 | 21-06-22    | 21-06-22 |
|      3 |   11 |           2 | 21-06-22    | NULL     |
|      4 |   12 |           0 | 21-06-22    | 21-06-22 |
|      5 |   12 |           1 | 21-06-22    | 21-06-22 |
|      6 |   12 |           2 | 21-06-22    | 21-06-22 |
|      7 |   12 |           3 | 21-06-22    | NULL     |
 -------- ------ ------------- ------------- ----------

Likewise, if any stage_code got decreased then we need to make start_date and end_date entries as NULL for that stage_code.

But the MERGE is inserting duplicate records even when e_id is loaded into the target table it is again loading that data.

Code:

MERGE INTO test_tab2 dst USING(
  WITH  got_new_code  AS
      (
        SELECT m.e_id,m.jira_key
        ,      m.stage_code
        ,      c.code
        FROM  test_tab1 m
        CROSS APPLY (
                    SELECT LEVEL - 1 AS code
                FROM   dual
                CONNECT BY LEVEL <= m.stage_code   1
                  )      c
      )
      SELECT   *
      FROM     got_new_code n
      FULL JOIN test_tab2 t USING (e_id,jira_key,stage_code))src
      ON (dst.e_id = src.e_id AND dst.stage_code = src.code AND dst.jira_key = src.jira_key)
      WHEN MATCHED THEN UPDATE
SET  dst.start_date = CASE
                  WHEN dst.stage_code <= src.stage_code
              THEN dst.start_date
              ELSE   NULL   -- optional (NULL is default) 
            END
,   dst.end_date  = CASE
                  WHEN dst.stage_code < src.stage_code
              THEN NVL (dst.end_date, SYSDATE)
              ELSE   NULL   -- optional (NULL is default) W
            END
  WHERE LNNVL (dst.stage_code < src.stage_code)
  OR   dst.end_date     IS NULL
WHEN NOT MATCHED
THEN INSERT (dst.seq_id,dst.e_id, dst.stage_code, dst.start_date, dst.end_date)
     VALUES (seq_id_sq.nextval,src.e_id, src.code, SYSDATE,        CASE
                           WHEN src.code < src.stage_code
                           THEN SYSDATE
                           ELSE NULL    -- optional (NULL is default) 
                         END
      )
;

CodePudding user response：

I have resolved the issue by doing the below changes in the code

MERGE INTO transact_tab  dst
USING (
      WITH  got_new_code  AS
      (
        SELECT m.e_id,m.jira_key
        ,      m.code AS new_code
        ,      c.code
        FROM  main_tab m
        CROSS APPLY (
                    SELECT LEVEL - 1 AS code -- subtracted 1 from level
                FROM   dual
                CONNECT BY LEVEL <= m.code   1 --Added   1
                  )      c
      )
      SELECT   *
      FROM     got_new_code n
      FULL JOIN transact_tab t USING (e_id,jira_key,code)
    )         src
ON (  dst.e_id = src.e_id
  AND dst.code = src.code
  )
WHEN MATCHED THEN UPDATE
SET  dst.start_date = CASE
                  WHEN dst.code <= src.new_code
              THEN dst.start_date
              ELSE   NULL   -- optional (NULL is default) 
            END
,   dst.end_date  = CASE
                  WHEN dst.code < src.new_code
              THEN NVL (dst.end_date, SYSDATE)
              ELSE   NULL   -- optional (NULL is default) W
            END
  WHERE LNNVL (dst.code < src.new_code)
  OR   dst.end_date     IS NULL
WHEN NOT MATCHED
THEN INSERT (dst.e_id, dst.code, dst.start_date, dst.end_date,dst.jira_key)
     VALUES (src.e_id, src.code, SYSDATE,        CASE
                           WHEN src.code < src.new_code
                           THEN SYSDATE
                           ELSE NULL    -- optional (NULL is default) 
                         END,src.jira_key
      )
;