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?