ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 粤磊 informatica powercenter学习笔记(十四)_中

粤磊 informatica powercenter学习笔记(十四)_中

原创 Linux操作系统 作者:vzyuelei9 时间:2011-04-11 20:38:01 0 删除 编辑

由于论坛字数限制,特分为上中下发布。

通过以上元数据表我们可以生成一些源数据管理用的报表

1 我需要找到所用的源数据对象可以通过下面QUERY来实现:

 

SELECT opb_subject.subj_name parent_subject_area,

          opb_subject.subj_id parent_subject_id,

          opb_src.source_name parent_source_name,

          opb_src.business_name parent_source_business_name,

          opb_src.src_id parent_source_id,

          opb_src.src_desc parent_source_description,

          opb_src.version_number parent_source_version_number,

          opb_src.version_status parent_source_version_status,

          opb_src.utc_checkin parent_source_utc_checkin,

          opb_src.utc_last_saved parent_source_utc_last_saved,

          opb_src.last_saved parent_source_last_saved,

          opb_src.file_name parent_source_schema_name,

          opb_src.fldid parent_source_first_field_id,

          opb_src.selid parent_source_select_info_id,

          opb_src.fdsiz parent_source_display_size,

          opb_src.pfdsiz parent_source_physical_size,

          opb_src.pfdsiz_min parent_src_min_physical_size,

          opb_dbd.dbdnam parent_source_database_name,

          opb_dbd.srctype parent_source_type,

          opb_mmd_dbtype.dbtype_name parent_source_database_type,

          opb_subject.subj_name subject_area, opb_subject.subj_id subject_id,

          opb_src.source_name source_name, opb_src.src_id source_id,

          opb_src.src_desc source_description,

          opb_src.version_number source_version_number,

          opb_src.version_status source_version_status,

          opb_src.utc_checkin source_utc_checkin,

          opb_src.utc_last_saved source_utc_last_saved,

          opb_src.last_saved source_last_saved,

          opb_dbd.dbdnam source_database_name, NULL repository_name,

          0 is_shortcut, 0 is_global_shortcut

     FROM opb_subject, opb_src, opb_dbd, opb_mmd_dbtype

    WHERE opb_src.subj_id = opb_subject.subj_id

      AND opb_subject.subj_id = opb_dbd.subj_id

      AND opb_src.dbdid = opb_dbd.dbdid

      AND opb_dbd.dbtype = opb_mmd_dbtype.dbtype_id

      AND opb_src.version_number = opb_dbd.version_number

      AND opb_src.is_visible = 1

      AND NOT EXISTS (

             SELECT '1'

               FROM opb_shortcut, opb_shortcut dbd

              WHERE opb_src.src_id = opb_shortcut.object_id

                AND opb_src.subj_id = opb_shortcut.subject_id

                AND opb_shortcut.object_type = 25

                AND opb_shortcut.is_visible = 1

                AND opb_shortcut.parent_id = dbd.object_id

                AND opb_shortcut.subject_id = dbd.subject_id

                AND opb_shortcut.version_number = dbd.version_number

                AND NOT (opb_shortcut.shortcut_name = dbd.shortcut_name)

                AND dbd.object_type = 22)

   UNION ALL

   SELECT PARENT.subj_name parent_subject_area,

          PARENT.subj_id parent_subject_id,

          opb_src.source_name parent_source_name,

          opb_src.business_name parent_source_business_name,

          opb_src.src_id parent_source_id,

          opb_src.src_desc parent_source_description,

          opb_src.version_number parent_source_version_number,

          opb_src.version_status parent_source_version_status,

          opb_src.utc_checkin parent_source_utc_checkin,

          opb_src.utc_last_saved parent_source_utc_last_saved,

          opb_src.last_saved parent_source_last_saved,

          opb_src.file_name parent_source_schema_name,

          opb_src.fldid parent_source_first_field_id,

          opb_src.selid parent_source_select_info_id,

          opb_src.fdsiz parent_source_display_size,

          opb_src.pfdsiz parent_source_physical_size,

          opb_src.pfdsiz_min parent_src_min_physical_size,

          opb_dbd.dbdnam parent_source_database_name,

          opb_dbd.srctype parent_source_type,

          opb_mmd_dbtype.dbtype_name parent_source_database_type,

          CHILD.subj_name subject_area, CHILD.subj_id subject_id,

          opb_shortcut.shortcut_name source_name,

          opb_shortcut.object_id source_id,

          opb_shortcut.comments source_description,

          opb_shortcut.version_number source_version_number,

          opb_shortcut.version_status source_version_status,

          opb_shortcut.utc_checkin source_utc_checkin,

          opb_shortcut.utc_last_saved source_utc_last_saved,

          opb_shortcut.creation_time source_last_saved,

          dbd.shortcut_name source_database_name,

          opb_shortcut.repository_name repository_name, 1 is_shortcut,

          0 is_global_shortcut

     FROM opb_shortcut,

          opb_subject PARENT,

          opb_subject CHILD,

          opb_src,

          opb_dbd,

          opb_mmd_dbtype,

          opb_shortcut dbd

    WHERE opb_shortcut.subject_id = CHILD.subj_id

      AND opb_shortcut.object_id = opb_src.src_id

      AND opb_shortcut.object_type = 25

      AND opb_shortcut.reference_type = 1

      AND opb_shortcut.is_visible = 1

      AND opb_src.subj_id = PARENT.subj_id

      AND opb_src.dbdid = opb_dbd.dbdid

      AND opb_dbd.dbtype = opb_mmd_dbtype.dbtype_id

      AND opb_src.version_number = opb_dbd.version_number

      AND opb_src.is_visible = 1

      AND opb_shortcut.parent_id = dbd.object_id

      AND opb_shortcut.subject_id = dbd.subject_id

      AND opb_shortcut.version_number = dbd.version_number

      AND NOT (opb_shortcut.shortcut_name = dbd.shortcut_name)

      AND dbd.object_type = 22

   UNION ALL

   SELECT NULL parent_subject_area, 0 parent_subject_id,

          NULL parent_source_name, NULL parent_source_business_name,

          0 parent_source_id, NULL parent_source_description,

          0 parent_source_version_number, 0 parent_source_version_status,

          0 parent_source_utc_checkin, 0 parent_source_utc_last_saved,

          NULL parent_source_last_saved, NULL parent_source_schema_name,

          0 parent_source_first_field_id, 0 parent_source_select_info_id,

          0 parent_source_display_size, 0 parent_source_physical_size,

          0 parent_src_min_physical_size, NULL parent_source_database_name,

          0 parent_source_type, NULL parent_source_database_type,

          opb_subject.subj_name subject_area, opb_subject.subj_id subject_id,

          opb_shortcut.shortcut_name source_name,

          opb_shortcut.object_id source_id,

          opb_shortcut.comments source_description,

          opb_shortcut.version_number source_version_number,

          opb_shortcut.version_status source_version_status,

          opb_shortcut.utc_checkin source_utc_checkin,

          opb_shortcut.utc_last_saved source_utc_last_saved,

          opb_shortcut.creation_time source_last_saved,

          dbd.shortcut_name source_database_name,

          opb_shortcut.repository_name repository_name, 1 is_shortcut,

          1 is_global_shortcut

     FROM opb_shortcut, opb_subject, opb_shortcut dbd

    WHERE opb_shortcut.subject_id = opb_subject.subj_id

      AND opb_shortcut.object_type = 25

      AND opb_shortcut.reference_type = 2

      AND opb_shortcut.is_visible = 1

      AND opb_shortcut.parent_id = dbd.object_id

      AND opb_shortcut.subject_id = dbd.subject_id

      AND opb_shortcut.version_number = dbd.version_number

      AND NOT (opb_shortcut.shortcut_name = dbd.shortcut_name)

      AND dbd.object_type = 22;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22377317/viewspace-692255/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-09-01

  • 博文量
    17
  • 访问量
    52756