Hi
Kings/Queens,
I need a small requirement. I just want to split the insert statements and wanted to know which target column is mapped with which source column.
The below insert statement is needed to be split
INSERT INTO TARGET
SELECT /*+ NO_MERGE */
L.AGMT_TRANS_PK_ID,
L.AGMT_GID,
L.FLAG_PK_ID,
L.DERIVED_FLAG_DESC,
L.CLASS_CD,
L.DOC_CNTNT_NARR,
L.INDUSTRY_CLASS_DESCRIPTOR_TXT,
L.INDUSTRY_DIV_DESC,
L.INDUSTRY_DIV_GRP_DESC
FROM (SELECT /*+ NO_MERGE */
DISTINCT
F.AGMT_TRANS_PK_ID AS AGMT_TRANS_PK_ID,
E.AGMT_GID AS AGMT_GID,
E.FLAG_PK_ID AS FLAG_PK_ID,
UPPER (J.DERIVED_FLAG_DESC) AS DERIVED_FLAG_DESC,
G.CLASS_CD AS CLASS_CD,
H.DOC_CNTNT_NARR AS DOC_CNTNT_NARR,
G.INDUSTRY_CLASS_DESCRIPTOR_TXT
AS INDUSTRY_CLASS_DESCRIPTOR_TXT,
K.INDUSTRY_DIV_DESC AS INDUSTRY_DIV_DESC,
K.INDUSTRY_DIV_GRP_DESC AS INDUSTRY_DIV_GRP_DESC,
ROW_NUMBER ()
OVER (PARTITION BY E.AGMT_GID, UPPER (J.DERIVED_FLAG_DESC)
ORDER BY E.FLAG_PK_ID DESC, H.DOC_CNTNT_NARR ASC)
AS ROW_ID
FROM (SELECT /*+ NO_MERGE */
DISTINCT
D.AGMT_GID, C.FLAG_PK_ID, C.AGMT_TRANS_PK_ID
FROM EDW_DM.AGMT_TRANS_FLAG_STOP_FACT C,
(SELECT /*+ NO_MERGE */
AGMT_GID, AGMT_TRANS_FLG_STP_PK_ID
FROM EDW_XFRM.D_AP_AGMT_TRANS_FLAG_STOP_FACT
UNION
SELECT /*+ NO_MERGE */
AGMT_GID, AGMT_TRANS_FLG_STP_PK_ID
FROM EDW_XFRM.D_ICF_TRANS_FLAG_STOP_FACT) D
WHERE C.AGMT_TRANS_FLG_STP_PK_ID =
D.AGMT_TRANS_FLG_STP_PK_ID
AND EXISTS
(SELECT 1
FROM ( SELECT /*+ NO_MERGE */
DISTINCT
B.AGMT_GID,
A.FLAG_PK_ID,
MAX (A.TRANS_PROCESS_TMSP)
TRANS_PROCESS_TMSP
FROM EDW_DM.
AGMT_TRANS_FLAG_STOP_FACT A,
(SELECT /*+ NO_MERGE */
AGMT_GID,
AGMT_TRANS_FLG_STP_PK_ID
FROM EDW_XFRM.
D_AP_AGMT_TRANS_FLAG_STOP_FACT
UNION
SELECT /*+ NO_MERGE */
AGMT_GID,
AGMT_TRANS_FLG_STP_PK_ID
FROM EDW_XFRM.
D_ICF_TRANS_FLAG_STOP_FACT) B
WHERE A.AGMT_TRANS_FLG_STP_PK_ID =
B.
AGMT_TRANS_FLG_STP_PK_ID
GROUP BY B.AGMT_GID, A.FLAG_PK_ID) A
WHERE D.AGMT_GID = A.AGMT_GID
AND C.FLAG_PK_ID = A.FLAG_PK_ID
AND C.TRANS_PROCESS_TMSP =
A.TRANS_PROCESS_TMSP)) E,
EDW_DM.AGREEMENT_TRANS_FACT F,
EDW_DM_DLV1.INDUSTRY_APPETITE_DIM_TW_MV G,
EDW_DM.FLAG_DOCUMENTATION_DIM H,
EDW_DM.AGMT_TRANS_FLAG_DOC_FACT I,
EDW_DM.FLAG_DIM J,
EDW_DM_DLV1.INDUSTRY_DIVISION_DIM_TW_MV K
WHERE E.AGMT_TRANS_PK_ID = F.AGMT_TRANS_PK_ID
AND E.FLAG_PK_ID = J.FLAG_PK_ID
AND F.INDUSTRY_APPETITE_PK_ID =
G.INDUSTRY_APPETITE_PK_ID_ORIG
AND F.INDUSTRY_DIV_PK_ID = K.INDUSTRY_DIV_PK_ID_ORIG
AND E.AGMT_TRANS_PK_ID = I.AGMT_TRANS_PK_ID(+)
AND I.FLAG_DOC_PK_ID = H.FLAG_DOC_PK_ID(+)) L
WHERE L.ROW_ID = 1;
COMMIT;
The out put should be in the below format
EXAMPLE:
|
| EMPLOYEE | COL1,COL2 | CASE WHEN COL2 IS NOT NULL THEN COL2 ELSE COL1 END | TAR_COL1 | TARGET_TABLE |
| EMPLOYEE | COL3 | (DIRECT LOAD) | TAR_COL2 | TARGET_TABLE |
| EMPLOYEE_TAB2 |
Coding can be done through PL/SQL procedures or Excel Macro.
Please help.