Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Inserting duplicate entries into the target table using MERGE

Albert ChaoJun 21 2022 — edited Jun 23 2022

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
      )
;
This post has been answered by Frank Kulash on Jun 21 2022
Jump to Answer

Comments

Answer

rqEval can accept non-numeric types as input.

In your example, 'select 1 "Industry" forces a returned numeric value.  It fails because the data returned contains 1 non-numeric column. The output doesn't match the specification in the SQL provided, and an error is returned.

Instead, use cast to return as varchar as follows:

SQL> select * from table(rqEval(

        NULL,

        'select cast(''Industry'' as varchar2(8)) "Industry" from dual',

       'Test'));

Returns:

1 Text

Marked as Answer by 3631507 · Sep 27 2020
1 - 1

Post Details

Added on Jun 21 2022
6 comments
518 views