automic V12.2 list of JOBP with sub JOBP and jobs

[marc broussard]

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

[marc broussard]

Hello

Any idea ?
I am looking for an easy way to get the list of JOBP with their JOBP (if there is) and their jobs, with the order.
And finally to have this liste  in an XLS file

by SQL or by another way:

JOBP1,     JOBX,  1
JOBP1,   JOBY  ,  2
JOBP1,   JOBP10, 3, JOBXX , 1
JOBP2 ...

which means:
in the JOBP1 we have 2 jobs (jobx and then joby), then still  in the JOBP1 we have a "sub JOBP" (JOBP10) with inside jobxx
...

thanks for help

[Juergen Lechner]

Good morning,
unfortunately there is no easy way to do this. You have the ID and the parent ID in one line. So for every level of your desired export you normally need to have a SQL-Level - means that you LEFT JOIN the records from a current level with all records that have a parent in it. This exercise needs to be repeated as many times as you have levels of sub-workflows.Starting point is the ID of the "root node". This is the parent workflow. The result is a SQL statement with a lot of sub selects below some sub selects... hmmm... or are they sob selects? ;-)

Regards
Juergen

------------------------------
Juergen Lechner
Senior Consultant
setis GmbH
Germany
------------------------------