Informatica各种对象的关系查询

来源：互联网发布：办公室网络拓扑图编辑：程序博客网时间：2024/05/20 13:38

查询所有主题（文件夹）名称：
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_IDSESSION_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_IDMAPPING_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

阅读全文

0 0