informatica元数据应用-查找表位置

适用场景：确定informatica中所适用表(table)的具体Mapping，session，workflow未知。

SELECT MAINLY.FOLDER_NAME   AS 文件夹,
       MAINLY.WORKFLOW_NAME AS WORKFLOW_NAME,
       MAINLY.SESSION_NAME  AS SESSION_NAME,
       MAINLY.MAPPING_NAME  AS MAPPING_NAME,
       SOURCE_NAME,
       TARGET_NAME,
       SQ.WIDGET_NAME       AS SQ组建名称,
       SQ.SQ                AS SQ组件中SQL,
       PRE.PRE_SQL          AS PRE_SQL内容,
       POST.POST_SQL        AS POST_SQL内容,
       LOOK.WIDGET_NAME     AS LOOKUP组建名称,
       LOOK.LOOK_TAB        AS LOOKUP组建表名
  FROM INFA_REPO.OPB_MAPPING MAPP
  JOIN (
        
        SELECT F.SUBJ_NAME       AS FOLDER_NAME,
                WF.TASK_NAME      AS WORKFLOW_NAME,
                SE.INSTANCE_NAME  AS SESSION_NAME,
                M.MAPPING_NAME,
                M.MAPPING_ID,
                SRC.SRC_ID,
                TGT.TARGET_ID,
                SRC.INST_SRC,
                TGT.INST_TAG,
                SRC.INSTANCE_NAME AS SOURCE_NAME,
                TGT.INSTANCE_NAME AS TARGET_NAME
          FROM INFA_REPO.OPB_SUBJECT F,
                INFA_REPO.OPB_TASK WF,
                (SELECT WORKFLOW_ID,
                        INSTANCE_ID,
                        TASK_ID,
                        TASK_TYPE,
                        INSTANCE_NAME,
                        MAX(VERSION_NUMBER)
                   FROM INFA_REPO.OPB_TASK_INST SESS
                  WHERE SESS.TASK_TYPE = 68
                  GROUP BY WORKFLOW_ID,
                           INSTANCE_ID,
                           TASK_ID,
                           TASK_TYPE,
                           INSTANCE_NAME) SE,
                (SELECT SESSION_ID, MAPPING_ID, MAX(VERSION_NUMBER)
                   FROM INFA_REPO.OPB_SESSION
                  GROUP BY SESSION_ID, MAPPING_ID) S,  --- SESSION名称
                INFA_REPO.OPB_MAPPING M,  ---mappingname
                (SELECT TAG1.MAPPING_ID,
                        SRC.SRC_ID,
                        SRC.SOURCE_NAME AS INSTANCE_NAME,
                        TAG1.INSTANCE_ID AS INST_SRC,
                        MAX(TAG1.VERSION_NUMBER)
                   FROM INFA_REPO.OPB_WIDGET_INST TAG1, INFA_REPO.OPB_SRC SRC
                  WHERE SRC.SRC_ID = TAG1.WIDGET_ID
                    AND TAG1.WIDGET_TYPE = 1
                  GROUP BY TAG1.MAPPING_ID,
                           SRC.SRC_ID,
                           SRC.SOURCE_NAME,
                           TAG1.INSTANCE_ID) SRC,   ---取源表名称
                (SELECT TAG1.MAPPING_ID,
                        TAG.TARGET_ID,
                        TAG.TARGET_NAME AS INSTANCE_NAME,
                        TAG1.INSTANCE_ID AS INST_TAG,
                        MAX(TAG1.VERSION_NUMBER)
                   FROM INFA_REPO.OPB_WIDGET_INST TAG1, INFA_REPO.OPB_TARG TAG
                  WHERE TAG.TARGET_ID = TAG1.WIDGET_ID
                    AND TAG1.WIDGET_TYPE = 2
                  GROUP BY TAG1.MAPPING_ID,
                           TAG.TARGET_ID,
                           TAG.TARGET_NAME,
                           TAG1.INSTANCE_ID) TGT   ---取目标表名
         WHERE WF.SUBJECT_ID = F.SUBJ_ID
           AND SE.WORKFLOW_ID = WF.TASK_ID
           AND WF.TASK_TYPE = 71
           AND SE.TASK_ID = S.SESSION_ID
           AND S.MAPPING_ID = M.MAPPING_ID
           AND M.IS_VISIBLE = 1
           AND SRC.MAPPING_ID = M.MAPPING_ID
           AND TGT.MAPPING_ID = M.MAPPING_ID
           /**AND F.SUBJ_ID IN (36, --文件夹ID，此处仅取想要的文件夹ID，使用者可根据需求
                             38,
                             39,
                             40,
                             41,
                             42,
                             53,
                             54,
                             55,
                             56,
                             58,
                             69 )**/) MAINLY
    ON MAPP.MAPPING_ID = MAINLY.MAPPING_ID
  LEFT JOIN (SELECT MAPPING_ID, WIDGET_ID, WIDGET_NAME, SQ
               FROM (SELECT MAPPING_ID,
                            WIDGET_ID,
                            WIDGET_NAME,
                            WM_CONCAT(ATTR_VALUE) OVER(PARTITION BY MAPPING_ID, WIDGET_ID, WIDGET_NAME ORDER BY LINE_NO ASC) AS SQ,
                            ROW_NUMBER() OVER(PARTITION BY MAPPING_ID, WIDGET_ID, WIDGET_NAME ORDER BY LINE_NO DESC) RN
                       FROM (SELECT INST.MAPPING_ID,
                                    T1.WIDGET_ID,
                                    T2.WIDGET_NAME,
                                    T1.LINE_NO AS LINE_NO,
                                    MIN(T1.ATTR_VALUE) AS ATTR_VALUE
                               FROM INFA_REPO.OPB_WIDGET_ATTR T1
                              INNER JOIN INFA_REPO.OPB_WIDGET T2
                                 ON T1.WIDGET_ID = T2.WIDGET_ID
                               JOIN INFA_REPO.OPB_WIDGET_INST INST
                                 ON INST.WIDGET_ID = T1.WIDGET_ID
                                AND INST.WIDGET_TYPE = T2.WIDGET_TYPE
                              WHERE T1.ATTR_ID = 1
                                AND T2.WIDGET_TYPE = 3
                              GROUP BY INST.MAPPING_ID,
                                       T1.WIDGET_ID,
                                       T2.WIDGET_NAME,
                                       T1.LINE_NO))
              WHERE RN = 1) SQ
    ON SQ.MAPPING_ID = MAINLY.MAPPING_ID
  LEFT JOIN (SELECT MAPPING_ID,
                    WIDGET_ID,
                    INSTANCE_ID,
                    LISTAGG(ATTR_VALUE, '') WITHIN GROUP(ORDER BY LINE_NO ASC) AS PRE_SQL  --- 表结构中PRE sql中的内容
               FROM (SELECT ATTR.MAPPING_ID,
                            ATTR.WIDGET_ID,
                            ATTR.INSTANCE_ID,
                            ATTR.LINE_NO,
                            MAX(ATTR.ATTR_VALUE) AS ATTR_VALUE
                       FROM INFA_REPO.OPB_MAPPING M
                       JOIN INFA_REPO.REP_WIDGET_ATTR ATTR
                         ON M.MAPPING_ID = ATTR.MAPPING_ID
                      WHERE ATTR.WIDGET_TYPE = 2
                        AND ATTR.ATTR_ID IN (6)
                        AND ATTR.ATTR_VALUE IS NOT NULL
                      GROUP BY ATTR.MAPPING_ID,
                               ATTR.WIDGET_ID,
                               ATTR.INSTANCE_ID,
                               ATTR.LINE_NO)
              GROUP BY MAPPING_ID, WIDGET_ID, INSTANCE_ID) PRE
    ON PRE.MAPPING_ID = MAPP.MAPPING_ID
   AND (PRE.WIDGET_ID = MAINLY.SRC_ID OR PRE.WIDGET_ID = MAINLY.TARGET_ID)
   AND (PRE.INSTANCE_ID = MAINLY.INST_SRC OR
       PRE.INSTANCE_ID = MAINLY.INST_TAG)
  LEFT JOIN (SELECT MAPPING_ID, WIDGET_ID, INSTANCE_ID, POST_SQL   --- 表结构中POST sql中的内容
               FROM (SELECT MAPPING_ID,
                            WIDGET_ID,
                            INSTANCE_ID,
                            WM_CONCAT(ATTR_VALUE) OVER(PARTITION BY MAPPING_ID, WIDGET_ID, INSTANCE_ID ORDER BY LINE_NO ASC) AS POST_SQL,
                            ROW_NUMBER() OVER(PARTITION BY MAPPING_ID, WIDGET_ID, INSTANCE_ID ORDER BY LINE_NO DESC) RN
                       FROM (SELECT ATTR.MAPPING_ID,
                                    ATTR.WIDGET_ID,
                                    ATTR.INSTANCE_ID,
                                    ATTR.LINE_NO,
                                    MAX(ATTR.ATTR_VALUE) AS ATTR_VALUE
                               FROM INFA_REPO.OPB_MAPPING M
                               JOIN INFA_REPO.REP_WIDGET_ATTR ATTR
                                 ON M.MAPPING_ID = ATTR.MAPPING_ID
                              WHERE ATTR.WIDGET_TYPE = 2
                                AND ATTR.ATTR_ID IN (7)
                                   /*AND M.SUBJECT_ID IN (36,38,39,40,41,42,53,54,55,56,58,69 )*/
                                AND ATTR.ATTR_VALUE IS NOT NULL
                              GROUP BY ATTR.MAPPING_ID,
                                       ATTR.WIDGET_ID,
                                       ATTR.INSTANCE_ID,
                                       ATTR.LINE_NO))
              WHERE RN = 1) POST
    ON POST.MAPPING_ID = MAPP.MAPPING_ID
   AND (POST.WIDGET_ID = MAINLY.SRC_ID OR POST.WIDGET_ID = MAINLY.TARGET_ID)
   AND (POST.INSTANCE_ID = MAINLY.INST_SRC OR
       POST.INSTANCE_ID = MAINLY.INST_TAG)
  LEFT JOIN (SELECT MAPPING_ID, WIDGET_NAME, LOOK_TAB
               FROM (SELECT MAPPING_ID,
                            WM_CONCAT(WIDGET_NAME) OVER(PARTITION BY MAPPING_ID) AS WIDGET_NAME,
                            WM_CONCAT(LOOK_TAB) OVER(PARTITION BY MAPPING_ID) AS LOOK_TAB,
                            ROW_NUMBER() OVER(PARTITION BY MAPPING_ID ORDER BY MAPPING_ID DESC) AS RN
                       FROM (SELECT MAPPING_ID,
                                    WIDGET_NAME,
                                    LISTAGG(LOOK_TAB, '') WITHIN GROUP(ORDER BY LINE_NO ASC) AS LOOK_TAB
                               FROM (SELECT INST.MAPPING_ID,
                                            T1.WIDGET_ID,
                                            T2.WIDGET_NAME,
                                            T1.LINE_NO AS LINE_NO,
                                            NVL(MIN(CASE
                                                      WHEN T1.ATTR_ID = 1 THEN
                                                       T1.ATTR_VALUE
                                                    END),
                                                MIN(CASE
                                                      WHEN T1.ATTR_ID = 2 THEN
                                                       T1.ATTR_VALUE
                                                    END)) LOOK_TAB
                                       FROM INFA_REPO.OPB_WIDGET_ATTR T1
                                      INNER JOIN INFA_REPO.OPB_WIDGET T2
                                         ON T1.WIDGET_ID = T2.WIDGET_ID
                                       JOIN INFA_REPO.OPB_WIDGET_INST INST
                                         ON INST.WIDGET_ID = T1.WIDGET_ID
                                        AND INST.WIDGET_TYPE = T2.WIDGET_TYPE
                                      WHERE T1.ATTR_ID IN (1, 2)  --- 1为lookUP组件表名，2位lookup组件SQL
                                        AND T2.WIDGET_TYPE = 11   ---LOOKUP类型
                                      GROUP BY INST.MAPPING_ID,
                                               T1.WIDGET_ID,
                                               T2.WIDGET_NAME,
                                               T1.LINE_NO)
                              GROUP BY MAPPING_ID, WIDGET_NAME))
              WHERE RN = 1) LOOK
    ON LOOK.MAPPING_ID = MAPP.MAPPING_ID
 ORDER BY MAINLY.FOLDER_NAME,
          MAINLY.WORKFLOW_NAME,
          MAINLY.SESSION_NAME,
          MAINLY.MAPPING_NAME