This is the extended version of the question : When I tried adding one additional column i.e jira_key it is loading duplicate entires.
https://community.oracle.com/tech/developers/discussion/comment/16839034#Comment_16839034
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(2,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 | JIRA_KEY | STAGE_CODE | START_DATE | END_DATE |
+--------+------+----------+-------------+-------------+----------+
| 1 | 11 | A | 0 | 21-06-22 | 21-06-22 |
| 2 | 11 | A | 1 | 21-06-22 | 21-06-22 |
| 3 | 11 | A | 2 | 21-06-22 | NULL |
| 4 | 12 | B | 0 | 21-06-22 | 21-06-22 |
| 5 | 12 | B | 1 | 21-06-22 | 21-06-22 |
| 6 | 12 | B | 2 | 21-06-22 | 21-06-22 |
| 7 | 12 | B | 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 those stage_code.
But the MERGE is inserting duplicate records even that e_id is loaded into the target table it is again loading that data.
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
)
;