Skip to Main Content

Split Insert statement

1053110Jun 17 2014 — edited Jun 17 2014

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:

SOURCE TABLESOURCE COLUMN NAMEDERIVATIONTARGET COLUMNTARGET COLUMN NAME
EMPLOYEECOL1,COL2CASE WHEN COL2 IS NOT NULL THEN COL2 ELSE COL1 ENDTAR_COL1TARGET_TABLE
EMPLOYEECOL3(DIRECT LOAD)TAR_COL2TARGET_TABLE
EMPLOYEE_TAB2

Coding can be done through PL/SQL procedures or Excel Macro.

Please help.

Comments
Post Details
Added on Jun 17 2014
2 comments
212 views