✍ Kashif M
Trends

Sunday, 6 September 2015


Query related to mapping in informatica


By on September 06, 2015


List mapping names


 SELECT   SUBJECT_AREA, PARENT_MAPPING_NAME
    FROM REP_ALL_MAPPINGS
ORDER BY 1, 2


List total count of mappings


 SELECT   SUBJECT_AREA, COUNT(PARENT_MAPPING_NAME) AS TOTAL_MAPPINGS
    FROM REP_ALL_MAPPINGS
      GROUP BY SUBJECT_AREA
 ORDER BY 1, 2


List last saved user for a mapping


 SELECT REP_SUBJECT.SUBJECT_AREA "FOLDER",REP_VERSION_PROPS.OBJECT_NAME "MAPPING", REP_USERS.USER_NAME,REP_VERSION_PROPS.LAST_SAVED
FROM REP_USERS,REP_VERSION_PROPS,REP_SUBJECT
WHERE REP_USERS.USER_ID=REP_VERSION_PROPS.USER_ID
 AND REP_VERSION_PROPS.OBJECT_TYPE IN (21)
 --AND REP_SUBJECT.SUBJECT_AREA = 'FOLDER_NAME'
 AND REP_SUBJECT.SUBJECT_ID = REP_VERSION_PROPS.SUBJECT_ID
ORDER BY 1,2,3,4

Informatica
Informatica


 List Mapping parameters and variables


select distinct rep_reposit_info.repository_name, rep_all_mappings.subject_area
as folder_name, rep_all_mappings.mapping_name as object_name,
case when opb_map_parmvar.pv_flag = 2 then 'Mapping Parameter' else
case when opb_map_parmvar.pv_flag = 3 then 'Mapping Variable' end
end as parameter_type, opb_map_parmvar.pv_name as parameter_name,
opb_map_parmvar.pv_default as parameter_value,
opb_map_parmvar.pv_desc as description
from rep_all_mappings, opb_map_parmvar, rep_reposit_info
where rep_all_mappings.mapping_id = opb_map_parmvar.mapping_id



List all the mappings using PARALLEL hints


SELECT S.SUBJ_NAME, M.MAPPING_NAME, W.WIDGET_NAME, A.WIDGET_ID, W.VERSION_NUMBER,
SUBSTR(A.ATTR_VALUE, 1, 60) ATTR_VALUE
FROM OPB_WIDGET_ATTR A, OPB_WIDGET W, OPB_SUBJECT S, OPB_WIDGET_INST I, OPB_MAPPING M
WHERE A.WIDGET_ID = W.WIDGET_ID
AND W.IS_VISIBLE = 1
AND A.VERSION_NUMBER = W.VERSION_NUMBER
AND A.WIDGET_TYPE IN(2, 3, 11) --Limit to Src/Tgt/Lkp Transformations
AND W.WIDGET_ID = I.WIDGET_ID
AND W.VERSION_NUMBER = I.VERSION_NUMBER
AND I.MAPPING_ID = M.MAPPING_ID
AND I.VERSION_NUMBER = M.VERSION_NUMBER
AND W.SUBJECT_ID = S.SUBJ_ID

AND UPPER(A.ATTR_VALUE) LIKE '%PARALLEL%'

Kashif
mkashu: Query related to mapping in informatica
Review : Kashif | Kashif
Update: September 06, 2015 | Rating: 4.5

Comment for "Query related to mapping in informatica"

0 comments

Post a Comment

Blog Archive