7.1
List
mapping names
SELECT SUBJECT_AREA, PARENT_MAPPING_NAME
FROM REP_ALL_MAPPINGS
ORDER BY 1, 2
7.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
7.3
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
7.4
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
7.5
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%'
No comments:
Post a Comment