Hello
For AUTOMIC 12.2 with MS SQL
on the forum I managed to find a query:
from a JOBP (a TOP JOBP) it gives the list of its sub JOBP (if there is) and of its jobs and the the jobs in the sub JOBP ...
=>
WITH OBJECTS(OH_NAME,OH_CLIENT,OH_IDNR,PARENT_IDNR,LEVEL)
AS
(
-- Anchor definition - the base record
select OH_NAME, OH_CLIENT, OH_IDNR, 0 "PARENT_IDNR", 0 "LEVEL"
from OH
where OH_DELETEFLAG = 0
and OH_REFIDNR = 0
and OH_CLIENT = xxxxx
and OH_NAME = 'TOP_JOBP'
UNION ALL
-- Get recursive members
select a.OH_NAME, a.OH_CLIENT, a.OH_IDNR, OBJECTS.OH_IDNR "PARENT_IDNR", OBJECTS.LEVEL+1 "LEVEL"
from OBJECTS, OH a, JPP b
where OBJECTS.OH_IDNR = b.jpp_oh_idnr
and a.OH_NAME = b.JPP_OBJECT
and a.OH_CLIENT = OBJECTS.OH_CLIENT
)
select * from OBJECTS;
now i am looking for improving this request with some additional information :
1/ how to have the place of the jobs (jpp_lnr) ?
2/ how to improve the presentation:
the best would be :
MASTER JOBP , JOBP1, job1.1
MASTER JOBP , JOBP1, job1.2
MASTER JOBP , JOBP2, job2.1
MASTER JOBP , JOBP3, jobp3.1,job3.1.1
MASTER JOBP , JOBP3, jobp3.1,job3.1.2
MASTER JOBP , JOBP4, job4.1
....
ANY IDEA?
thanks
Marc Broussard
ENGIE INEO