Krum_Ganev

SQL statement for activity window cleanup

Discussion created by Krum_Ganev on Jun 6, 2017
Latest reply on Jun 6, 2017 by Antoine_Sauteron_1266
We are building new Automic environment for new customer.

We will need SQL statement for activity windows cleanup.
We was able to find below : 

WITH subtasks (S_IDNR, S_PARENT, S_CLIENT, S_NAME, S_STATUS, S_OTYPE) as (
SELECT eh.EH_AH_Idnr , eh.EH_ParentAct , eh.EH_Client , eh.EH_Name , eh.eh_status , eh.eh_otype 
 FROM EH
WHERE eh_status <> 1900
and eh_client = ?

    UNION ALL

  SELECT eh.EH_AH_Idnr, eh.EH_ParentAct, eh.EH_Client, eh.EH_Name, eh.eh_status, eh.eh_otype
  FROM EH 
  INNER JOIN subtasks 
      on eh.EH_AH_Idnr = subtasks.S_PARENT
),
eh_hierarchy (H_IDNR, H_PARENT, H_CLIENT, H_NAME, H_STATUS, H_OTYPE, H_LEVEL) as (
SELECT eh.EH_AH_Idnr , eh.EH_ParentAct , eh.EH_Client , eh.EH_Name , eh.eh_status , eh.eh_otype , 0 AS "H_LEVEL"
FROM EH
WHERE eh_otype in ('JOBP', 'JSCH')
  and eh_parentprc = 0
  and eh_client = ?

    UNION ALL

  SELECT eh.EH_AH_Idnr, eh.EH_ParentAct, eh.EH_Client, eh.EH_Name, eh.eh_status, eh.eh_otype, H_LEVEL + 1
  FROM EH
  INNER JOIN eh_hierarchy 
      ON eh.EH_ParentAct = eh_hierarchy.H_IDNR
)
SELECT eh.EH_AH_Idnr, eh.EH_ParentAct, eh.EH_Client, eh.EH_Name, eh.eh_status, eh.eh_otype, H_LEVEL 
FROM   eh, eh_hierarchy
WHERE eh.eh_ah_idnr not in (SELECT subtasks.S_IDNR FROM subtasks)
  and eh.eh_otype = 'JOBP'
  and eh.eh_client = ?
  and eh.eh_ah_idnr = eh_hierarchy.H_IDNR
order by H_LEVEL desc


This is for SQL database and we are working with Oracle now.
Is it suitable to use the same or some lines should be altered? 

Outcomes