Extract the metadata extensions data from your ... Skip navigation

Extract the metadata extensions data from your repository

score 0
You have not voted. Active

We are constantly using metadata extensions and need the data in our datamodel. It would be handy to have powercenters standard mappings instead of below SQL.

 

On Mapping level we use:

SELECT  METADATA_EXTN_NAME,

        METADATA_EXTN_VALUE,

        FOLDER_NAME,

        MAPPING_NAME,

        mapping_name

FROM    REP_METADATA_EXTNS me JOIN

(

SELECT f.subj_name      AS folder_name,

       wf.task_name     AS workflow_name,

       wf.comments      AS workflow_comments,

       se.instance_name AS session_name,

       ma.mapping_name  AS mapping_name,

       ma.comments      AS mapping_comments,

       workflow_id AS WORKFLOW_ID,

       ma.mapping_id AS MAPPING_ID,

       ma.subject_id AS SUBJECT_ID,

       ma.last_saved    AS mapping_last_saved,

       wf.last_saved    AS workflow_last_saved

FROM   opb_subject f

       JOIN opb_task wf                          ON wf.subject_id = f.subj_id

       JOIN (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 ON se.workflow_id = wf.task_id

       JOIN (SELECT session_id,

                    mapping_id,

                    MAX(version_number)

             FROM   opb_session

             GROUP  BY session_id,

                       mapping_id) s     ON se.task_id = s.session_id

       JOIN opb_mapping ma                      ON s.mapping_id = ma.mapping_id

WHERE  wf.is_visible = 1

       AND wf.task_type = 71

       AND ma.is_visible = 1

       AND f.subj_name like '@FOLDER@%'   --- Replace by the foldername you want to get the info from

) mp

on me.metadata_extn_object_id = mp.mapping_id and me.subject_id = mp.subject_id

 

On Workflow level we use:

SELECT  METADATA_EXTN_NAME,

        METADATA_EXTN_VALUE,

        FOLDER_NAME,

        WORKFLOW_NAME

FROM    REP_METADATA_EXTNS me JOIN

(

SELECT f.subj_name      AS folder_name,

       wf.task_name     AS workflow_name,

       wf.comments      AS workflow_comments,

       wf.SUBJECT_ID    as subject_id,

       wf.TASK_ID       as task_id,

       wf.last_saved    AS workflow_last_saved

FROM   opb_subject f

       JOIN opb_task wf                          ON wf.subject_id = f.subj_id

WHERE  wf.is_visible = 1

       AND wf.task_type = 71

       AND f.subj_name like '@FOLDER@%'   --- Replace by the foldername you want to get the info from

 

) mp

on me.metadata_extn_object_id = mp.task_id and me.subject_id = mp.subject_id

Comments

Vote history