By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE DELIGATE_DETAILS_MAIN
( E_ID NUMBER(10,0),
COMPLETED_DATE TIMESTAMP (6),
CONSTRAINT PK_DELIGATE_DETAILS_MAIN PRIMARY KEY (E_ID));
Insert into deligate_details_main (E_ID,COMPLETED_DATE) values (1,to_timestamp('13-12-21 6:05:23.991000000 PM','DD-MM-RR fmHH12:fmMI:SSXFF AM'));
1 rows affected
Insert into deligate_details_main (E_ID,COMPLETED_DATE) values (2,to_timestamp('13-12-21 6:05:24.019000000 PM','DD-MM-RR fmHH12:fmMI:SSXFF AM'));
1 rows affected
Insert into deligate_details_main (E_ID,COMPLETED_DATE) values (3,to_timestamp('13-12-21 6:05:24.029000000 PM','DD-MM-RR fmHH12:fmMI:SSXFF AM'));
1 rows affected
Insert into deligate_details_main (E_ID,COMPLETED_DATE) values (4,to_timestamp('13-12-21 10:46:00.015000000 PM','DD-MM-RR fmHH12:fmMI:SSXFF AM'));
1 rows affected
CREATE TABLE CONTROL_MAIN
( E_ID NUMBER(10,0),
E_SPEC VARCHAR2(30 BYTE),
CONSTRAINT PK_CONTROL_MAIN PRIMARY KEY (E_ID));
Insert into CONTROL_MAIN (E_ID,E_SPEC) values (1,'SAP1');
1 rows affected
Insert into CONTROL_MAIN (E_ID,E_SPEC) values (2,'FSAP');
1 rows affected
Insert into CONTROL_MAIN (E_ID,E_SPEC) values (3,'SAP2');
1 rows affected
Insert into CONTROL_MAIN (E_ID,E_SPEC) values (4,'SAP1-480');
1 rows affected
CREATE TABLE QUESTION
( E_ID NUMBER(10,0),
QUEST VARCHAR2(30 BYTE),
CONSTRAINT PK_QUESTION PRIMARY KEY (E_ID));
Insert into QUESTION (E_ID,QUEST) values (1,'Yes');
1 rows affected
Insert into QUESTION (E_ID,QUEST) values (2,'No');
1 rows affected
Insert into QUESTION (E_ID,QUEST) values (3,'Yes');
1 rows affected
Insert into QUESTION (E_ID,QUEST) values (4,'Yes');
1 rows affected
CREATE TABLE DELIGATE_DETAILS_TRANS
( D_ID NUMBER(10,0),
E_ID NUMBER(10,0),
COMPLETED_DATE_TRANS DATE,
OWNER_DETAIL VARCHAR2(30 BYTE),
CONSTRAINT PK_DELIGATE_DETAILS_TRANS PRIMARY KEY (D_ID),
CONSTRAINT FK_E_ID FOREIGN KEY (E_ID)
REFERENCES DELIGATE_DETAILS_MAIN (E_ID));
CREATE SEQUENCE deligate_details_trans_sq;
MERGE INTO deligate_details_trans t
USING (
SELECT
ddm.e_id,
ddm.completed_date,
cm.e_spec
FROM
deligate_details_main ddm
JOIN control_main cm ON ( cm.e_id = ddm.e_id AND cm.e_spec LIKE '%SAP%' )
JOIN question q ON ( q.e_id = ddm.e_id
AND q.quest = 'Yes' )
) s
ON (t.e_id = s.e_id)
WHEN NOT MATCHED THEN INSERT (
d_id,e_id, completed_date_trans, owner_detail
)
VALUES (
deligate_details_trans_sq.nextval,
s.e_id,
CAST(s.completed_date AS DATE),
CASE s.e_spec
WHEN 'SAP1' THEN 'SAP1'
WHEN 'SAP2' THEN 'SAP2'
WHEN 'SAP1-480' THEN 'SAP3'
END
);
3 rows affected
SELECT * FROM deligate_details_trans;
| D_ID | E_ID | COMPLETED_DATE_TRANS | OWNER_DETAIL |
|---|---|---|---|
| 1 | 4 | 13-DEC-21 | SAP3 |
| 2 | 3 | 13-DEC-21 | SAP2 |
| 3 | 1 | 13-DEC-21 | SAP1 |
ROLLBACK;
MERGE INTO deligate_details_trans t
USING (
SELECT
ddm.e_id,
ddm.completed_date,
CASE cm.e_spec
WHEN 'SAP1' THEN 'SAP1'
WHEN 'SAP2' THEN 'SAP2'
WHEN 'SAP1-480' THEN 'SAP3'
END AS owner_detail
FROM
deligate_details_main ddm
JOIN control_main cm ON ( cm.e_id = ddm.e_id AND cm.e_spec LIKE '%SAP%' )
JOIN question q ON ( q.e_id = ddm.e_id
AND q.quest = 'Yes' )
) s
ON (t.e_id = s.e_id)
WHEN NOT MATCHED THEN INSERT (
d_id,e_id, completed_date_trans, owner_detail
)
VALUES (
deligate_details_trans_sq.nextval,
s.e_id,
CAST(s.completed_date AS DATE),
s.owner_detail
);
3 rows affected
SELECT * FROM deligate_details_trans;
| D_ID | E_ID | COMPLETED_DATE_TRANS | OWNER_DETAIL |
|---|---|---|---|
| 4 | 4 | 13-DEC-21 | SAP3 |
| 5 | 3 | 13-DEC-21 | SAP2 |
| 6 | 1 | 13-DEC-21 | SAP1 |