Posted Fri, 28 Aug 2015 13:24:20 GMT by Portal Admin

select dep_jobs.name,
       dependencies.conjunction,
       CASE
          WHEN dependencies.prereq_object_type = 'Job'                THEN pre_jobs.name
          WHEN dependencies.prereq_object_type = 'JobSuite'           THEN pre_jobs.name
          WHEN dependencies.prereq_object_type = 'JobSuiteMemberJob'  THEN pre_jobs.name
          WHEN dependencies.prereq_object_type = 'MemberJob'          THEN pre_jobs.name
          WHEN dependencies.prereq_object_type = 'RemoteEvent'        THEN pre_remote_events.remote_object_name
          WHEN dependencies.prereq_object_type = 'AgentEventMonitor'  THEN pre_aems.name
          WHEN dependencies.prereq_object_type = 'SapEventMonitor'    THEN pre_sems.name
          WHEN dependencies.prereq_object_type = 'SnmpTrapMonitor'    THEN pre_snmp.name
          ELSE ''
       END prereq_name,
       CASE
          WHEN dependencies.prereq_type = 0 THEN 'Job Status'
          WHEN dependencies.prereq_type = 1 THEN 'Agent Event Monitor Event'
          WHEN dependencies.prereq_type = 2 THEN 'Job Monitor Event'
          WHEN dependencies.prereq_type = 3 THEN 'SNMP Trap Event'
          WHEN dependencies.prereq_type = 4 THEN 'Job Change'
          WHEN dependencies.prereq_type = 5 THEN 'Suite Member Status'
          WHEN dependencies.prereq_type = 6 THEN 'Suite Monitor Event'
          WHEN dependencies.prereq_type = 7 THEN 'Suite Status'
          WHEN dependencies.prereq_type = 8 THEN 'SAP Monitor Event'
          WHEN dependencies.prereq_type = 9 THEN 'Suite Member Monitor'
          WHEN dependencies.prereq_type = 10 THEN 'Remote Event'
          ELSE ''
       END prereq_subtype,
       CASE
          WHEN dependencies.react_to_status = 'S' THEN 'Submitted'
          WHEN dependencies.react_to_status = 'H' THEN 'Condition Check'
          WHEN dependencies.react_to_status = 'W' THEN 'Condition Retry Wait'
          WHEN dependencies.react_to_status = 'R' THEN 'Running'
          WHEN dependencies.react_to_status = 'F' THEN 'Failed'
          WHEN dependencies.react_to_status = 'C' THEN 'Completed'
          WHEN dependencies.react_to_status = 'A' THEN 'Canceled'
          WHEN dependencies.react_to_status = 'K' THEN 'Skipped'
          WHEN dependencies.react_to_status = 'D' THEN 'Ended (Complete/Cancel/Fail)'
          WHEN dependencies.react_to_status = 'U' THEN 'Underrun'
          WHEN dependencies.react_to_status = 'O' THEN 'Overrun'
          WHEN dependencies.react_to_status = 'L' THEN 'Late Start'
          WHEN dependencies.react_to_status = '*' THEN 'Occurred'
          ELSE dependencies.react_to_status
       END prereq_status,
       dependencies.sequence_number,
       dependencies.prereq_object_type prereq_type
from dependencies
join jobs dep_jobs on dependencies.dependent_job_id = dep_jobs.id
left outer join jobs pre_jobs on dependencies.prereq_object_id = pre_jobs.id
left outer join remote_events pre_remote_events on dependencies.prereq_object_id = pre_remote_events.id
left outer join agent_event_monitors pre_aems on dependencies.prereq_object_id = pre_aems.id
left outer join snmp_trap_monitors pre_snmp on dependencies.prereq_object_id = pre_snmp.id
left outer join sap_event_monitors pre_sems on dependencies.prereq_object_id = pre_sems.id
order by dep_jobs.name, dependencies.sequence_number

You must be signed in to post in this forum.