Mapping Lookup Transformations

Displays Lookup transformations used in a mapping by repository and folder.

Click on the text to copy the query report to your clipboard.


select case when ("WIDGET_VERSION_STATUS" = 0) then ('Active') else (case when ("WIDGET_VERSION_STATUS" = 9) then ('Logically Deleted') else (case when ("WIDGET_VERSION_STATUS" = 10) then ('Physically Deleted') else ('NULL') end) end) end as "Calc_WIDGET_VERSION_STATUS",
"ATTR_VALUE",
case when ("PARENT_WIDGET_IS_REUSABLE" = 1) then ('Yes') else ('No') end as "Calc_PARENT_WIDGET_IS_REUSABLE",
case when ("IS_SHORTCUT" = 1) then ('Yes') else ('No') end as "Calc_IS_SHORTCUT",
"MAPPING_NAME",
"SUBJECT_AREA",
"WIDGET_NAME",
"INSTANCE_NAME",
"DESCRIPTION",
"WIDGET_VERSION_NUMBER",
"WIDGET_TYPE_NAME",
"REPOSITORY_NAME"
from (SELECT  DISTINCT MAPPING_REPOSIT_INFO.REPOSITORY_NAME,  ALL_MAPPINGS.SUBJECT_AREA,  ALL_MAPPINGS.MAPPING_NAME,  MAPPING_WIDGET_INST.INSTANCE_NAME,  MAPPING_ALL_TRANSFORMS.WIDGET_NAME,  MAPPING_WIDGET_INST.DESCRIPTION as DESCRIPTION,  CASE    WHEN MAPPING_TRANSFORM_WIDGET_ATTR.ATTR_DATATYPE IN (1,   4)    THEN    CASE     WHEN MAPPING_TRANSFORM_WIDGET_ATTR.ATTR_TYPE = 5     THEN     CASE      WHEN MAPPING_TRANSFORM_WIDGET_ATTR.ATTR_VALUE = 1      THEN 'Yes'      ELSE 'No'     END      ELSE MAPPING_TRANSFORM_WIDGET_ATTR.ATTR_VALUE   END    ELSE MAPPING_TRANSFORM_WIDGET_ATTR.ATTR_VALUE   END ATTR_VALUE,       MAPPING_ALL_TRANSFORMS.WIDGET_VERSION_NUMBER,     MAPPING_ALL_TRANSFORMS.WIDGET_VERSION_STATUS,      MAPPING_ALL_TRANSFORMS.PARENT_WIDGET_IS_REUSABLE,     MAPPING_ALL_TRANSFORMS.IS_SHORTCUT,        MAPPING_ALL_TRANSFORMS.WIDGET_TYPE_NAME  FROM  REP_ALL_MAPPINGS ALL_MAPPINGS,  REP_REPOSIT_INFO MAPPING_REPOSIT_INFO,  REP_WIDGET_INST MAPPING_WIDGET_INST,  REP_WIDGET_ATTR MAPPING_TRANSFORM_WIDGET_ATTR,  REP_ALL_TRANSFORMS MAPPING_ALL_TRANSFORMS  WHERE  (ALL_MAPPINGS.PARENT_MAPPING_ID <> MAPPING_REPOSIT_INFO.REPOSITORY_ID AND  ALL_MAPPINGS.PARENT_MAPPING_ID = MAPPING_WIDGET_INST.MAPPING_ID AND  ALL_MAPPINGS.PARENT_MAPPING_VERSION_NUMBER = MAPPING_WIDGET_INST.  VERSION_NUMBER AND  ALL_MAPPINGS.PARENT_SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND  MAPPING_WIDGET_INST.WIDGET_ID = MAPPING_TRANSFORM_WIDGET_ATTR.WIDGET_ID AND  MAPPING_WIDGET_INST.WIDGET_TYPE = MAPPING_TRANSFORM_WIDGET_ATTR.WIDGET_TYPE   AND  MAPPING_TRANSFORM_WIDGET_ATTR.SESSION_TASK_ID = 0 AND  MAPPING_TRANSFORM_WIDGET_ATTR.WIDGET_TYPE NOT IN (1,  2,  44) AND  MAPPING_ALL_TRANSFORMS.PARENT_WIDGET_ID = MAPPING_WIDGET_INST.WIDGET_ID AND  MAPPING_ALL_TRANSFORMS.SUBJECT_ID = MAPPING_WIDGET_INST.SUBJECT_ID AND  MAPPING_ALL_TRANSFORMS.WIDGET_TYPE_ID = MAPPING_WIDGET_INST.WIDGET_TYPE )   AND  (MAPPING_ALL_TRANSFORMS.WIDGET_TYPE_NAME = 'Lookup Procedure' AND  MAPPING_TRANSFORM_WIDGET_ATTR.ATTR_ID = 2 )) "query"
where "SUBJECT_AREA" in ('<Folder_Name>') and "MAPPING_NAME" in ('<Mapping_Name>')
order by "REPOSITORY_NAME", "SUBJECT_AREA"