Informatica各种对象的关系查询

最新推荐文章于 2021-11-24 11:43:53 发布

RedLeague

最新推荐文章于 2021-11-24 11:43:53 发布

阅读量1k

点赞数

分类专栏： Informatica 文章标签：元数据 informatica

本文链接：https://blog.csdn.net/sun120204535/article/details/77447031

版权

Informatica 专栏收录该内容

19 篇文章 2 订阅

订阅专栏

查询所有主题（文件夹）名称：
SELECT DISTINCT SUBJ_NAME FROM OPB_TASK
INNER JOIN OPB_SUBJECT ON OPB_TASK.SUBJECT_ID=OPB_SUBJECT.SUBJ_ID
ORDER BY SUBJ_NAME
查询全部主题（文件夹）对应的所有工作流（表OPB_TASK、OPB_SUBJECT ）：
SELECT distinct SUBJECT_ID,SUBJ_NAME,SUBJECT_ID,TASK_NAME
FROM OPB_TASK
INNER JOIN OPB_SUBJECT ON OPB_TASK.SUBJECT_ID=OPB_SUBJECT.SUBJ_ID

WHERE SUBSTRING(TASK_NAME,1,3)='wf_' ORDER BY SUBJ_NAME,TASK_NAME

TASK_NAME - TASK_ID - WORKFLOW_ID - SESSION_ID - MAPPING_ID - TARGET_ID、SOURCE_ID

TASK_ID -> WORKFLOW_ID :SELECT WORKFLOW_ID,INSTANCE_ID,TASK_ID,TASK_TYPE,INSTANCE_NAME,MAX(VERSION_NUMBER)
                         FROM OPB_TASK_INST SESS
                        WHERE SESS.TASK_TYPE = 68
                        GROUP BY WORKFLOW_ID,
                                 INSTANCE_ID,
                                 TASK_ID,
                                 TASK_TYPE,
                                 INSTANCE_NAME   
WORKFLOW_ID -> SESSION_ID: SELECT DISTINCT WORKFLOW_ID,SESSION_ID FROM 
                           (SELECT WORKFLOW_ID,INSTANCE_ID,TASK_ID,TASK_TYPE,INSTANCE_NAME,MAX(VERSION_NUMBER)
                             FROM 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 OPB_SESSION
                              GROUP BY SESSION_ID, MAPPING_ID) S  
                            WHERE SE.TASK_ID = S.SESSION_ID
SESSION_ID  -> MAPPING_ID :SELECT DISTINCT  SESSION_ID,M.MAPPING_ID FROM
                               (SELECT SESSION_ID, MAPPING_ID, MAX(VERSION_NUMBER)
                                         FROM OPB_SESSION
                                        GROUP BY SESSION_ID, MAPPING_ID) S,
                                      OPB_MAPPING M
                           WHERE S.MAPPING_ID = M.MAPPING_ID

MAPPING_ID  -> TARGET_ID、SOURCE_ID  : SELECT DISTINCT M.MAPPING_NAME,INSTANCE_NAME
                                         FROM OPB_MAPPING M, 
                                           (SELECT MAPPING_ID, INSTANCE_NAME, WIDGET_TYPE, MAX(VERSION_NUMBER)
                                              FROM OPB_WIDGET_INST
                                              WHERE WIDGET_TYPE = 2
                                              GROUP BY MAPPING_ID, INSTANCE_NAME, WIDGET_TYPE) TGT
                                        WHERE TGT.MAPPING_ID = M.MAPPING_ID