Automic Workload Automation

  • Private Community
 View Only
Back to discussions
Expand all | Collapse all

SQL query to find predecessor and successor

  • 1.  SQL query to find predecessor and successor

    Posted Mar 14, 2018 10:27 AM
    Hi,

    I am looking for a request to trace the jobs present in "time & dependencies".
    I can get the status, but not the name of the objects.

    The best, if is it possible, i would like this result:
    Predecessor, successors, time & dependancies, status

    Sorry if the question is already asked... I cannot find the answer on this board...

    2z0c2owcu8bd.pnghttps://us.v-cdn.net/5019921/uploads/editor/xl/2z0c2owcu8bd.png" width="1592">

    Thank for you help !


  • 2.  SQL query to find predecessor and successor

    Posted Mar 14, 2018 10:34 AM
    Hi

    do you mean in general (defined) or at execution time?

    cheers, Wolfgang


  • 3.  SQL query to find predecessor and successor

    Posted Mar 14, 2018 10:51 AM
    At the execution time (past execution).
    I suppose values are in Archives tables...


  • 4.  SQL query to find predecessor and successor

    Posted Mar 14, 2018 11:01 AM
    I would have a deeper look into the AJPP table.

    cheers, Wolfgang


  • 5.  SQL query to find predecessor and successor

    Posted Mar 14, 2018 11:15 AM
    Sorry typo....

    its the AJPPA table...

    Think there would be a combination of AJPPA and AJPP Table useful for your request....

    cheers, Wolfgang





  • 6.  SQL query to find predecessor and successor

    Posted Mar 14, 2018 12:03 PM
    Thanks, apreciate your help Wolfgang

    First query, I find the Object ID in AH table.
    Second query, select * with where clause with ID.
    vug1xa6ioifs.pnghttps://us.v-cdn.net/5019921/uploads/editor/eu/vug1xa6ioifs.png" width="875">

    I cannot understand the return value and match the relation with asc / desc.

    In the database schema I found.
    AJPPA_Lnr : ascending number of the jobplan after
    AJPPA_PreLnr : ascending number of the precondition

    I think, this is the job number in workflow but not the asc/dec ID.








  • 7.  SQL query to find predecessor and successor

    Posted Mar 15, 2018 04:27 AM
    Buenos Morgos,

    you can try this one but please doublecheck, I did only a few tests for consistency with it....
    change the Run ID to your needs...
    select AJPP_AH_IDNR as 'Workflow run ID', AJPP_LNR as 'Object Position',AJPP_OBJECT as 'Task Name', ajpp_taskidnr as "Task RunID", ajppa_when as "Dependency tp Predecessor"  from ajpp, ajppa
    where ajpp_ah_idnr = 1661002
    and ajppa_ajpp_lnr = ajpp_lnr
    and AJPPA_AH_Idnr = ajpp_ah_idnr
    and ajpp_object not in ('START')

    cheers, Wolfgang


  • 8.  SQL query to find predecessor and successor

    Posted Mar 19, 2018 11:28 AM
    Hi,

    Thank for your investigations. I tried your query this morning.
    Is it possible to add in the result the name or ID ot the dépendancy task ? I cannot found them in tables.


  • 9.  SQL query to find predecessor and successor

    Posted Mar 19, 2018 04:39 PM
    Hi

    sry that exceeds my SQL knowledge :-(

    Basically you should decrement ajpp_lnr by one and find the corresponding  ajpp_ah_idnr - thats the predecessor...

    cheers, Wolfgang


  • 10.  SQL query to find predecessor and successor

    Posted Mar 20, 2018 05:24 AM
    No worries.
    If i solve this i post the query here.
    Thanks for helping me.


  • 11.  Re: SQL query to find predecessor and successor

    Posted Aug 01, 2018 06:31 AM

    Hey, Were you able to solve this and create a query which gives us all predecessors and successors of each job in a jobplan.

     

    Thanks,

    Shashank



  • 12.  RE: Re: SQL query to find predecessor and successor

    Posted Jan 28, 2021 04:37 PM
    Please share your queries with the community.
    Be Well,
    Phil Frankford

    ------------------------------
    DBA/Systems Engineer
    Colony Brands
    ------------------------------

    Original Message