Sponsored Links
Sponsored Link

sponsored links

Collapse

Announcement

Collapse
No announcement yet.

Pretty formatting options?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Pretty formatting options?

    I shamelessly stole Birgitta's script to parse a recursive SQL lookup to build a nested set of data (Thanks B - awesome stuff even if I don't understand it properly) and that works a dream.

    I have a table listing job submission details for a subsystem i am watching, and I am using the SQL to list the jobs based on parent/child relationship nesting to show the tree of what submitted what. Running the SQL over my test data I get the result as per the attached screenshot.

    Job added to table is JBNMBR/JBUSER/JBNAME
    Parent job is JBPNBR/JBPUSR/JBPNAM
    Ancestor job (which is the first or root job in each tree that was logged) is JBANBR/JBAUSR/JBANAM

    SQL is;
    Code:
    With JobList (
            Level,  jbsbsn, jbname, jbuser, jbnmbr, jbqlib, jbqnam, jbzent, jbzstr, jbzend, jbtype, jbsubt, jbecod, jbcpu#, 
            jbpnam, jbpusr, jbpnbr, jbanam, jbausr, jbanbr
        ) as (
          -- Fetch ancestor record that matches the given job details
          Select 
                1, jbsbsn, jbname, jbuser, jbnmbr, jbqlib, jbqnam, jbzent, jbzstr, jbzend, jbtype, jbsubt, jbecod, jbcpu#, 
                jbpnam, jbpusr, jbpnbr, jbanam, jbausr, jbanbr
            From autojobs
            --  Select ancestor job based on the nominated target job (can be anywhere in tree)
            Where jbname = (
                select jbanam from autojobs 
                where jbname = 'JOB_4_3' 
                and jbuser = 'GREG' 
                and jbnmbr = '881874'
              ) 
              and jbuser = (select jbausr from autojobs where jbname = 'JOB_4_3' and jbuser = 'GREG' and jbnmbr = '881874')
              and jbnmbr = (select jbanbr from autojobs where jbname = 'JOB_4_3' and jbuser = 'GREG' and jbnmbr = '881874')
          Union All
          -- Then fetch all subsequent jobs from the ancestors job tree
          Select Level + 1, m.jbsbsn, m.jbname, m.jbuser, m.jbnmbr, m.jbqlib, m.jbqnam, m.jbzent, m.jbzstr, m.jbzend, 
                m.jbtype, m.jbsubt, m.jbecod, m.jbcpu#, m.jbpnam, m.jbpusr, m.jbpnbr, m.jbanam, m.jbausr, m.jbanbr
            from JobList j join autojobs m on  j.jbnmbr = m.jbpnbr
    ) 
    -- Search Clause
    Search Depth First By jbnmbr set Sort
    -- Final Select
    Select level, ROW_NUMBER() OVER(order by level) as rownumber,
        jbsbsn, 
        case level when 1 then jbname else concat(concat(repeat('┃ ',Level-2),'┣╸'),jbname) end as jbname, 
        jbuser, jbnmbr, jbqlib, jbqnam, jbecod, jbcpu#
    From JobList 
    ORDER BY sort;
    As seen in the attached shot it does what i say, but I would prefer to be able to remove the highlighted symbols showing the tree as they have no "child" jobs after that point.

    Click image for larger version

Name:	job list.png
Views:	1
Size:	35.4 KB
ID:	148165

    Yes I know this is not required as the data has been returned in the correct sequence etc, but I am curious if there is a way to do this

    I also know I have included more fields than I am returning ... this was to verify that everything was correct ... final copy only has what is used.

    Cheers
    Greg

    Greg Craill: "Life's hard - Get a helmet !!"

  • #2
    Further to the above - how come job 881868/GREG/JOB_4_1 is listed after jobs 2, 3, 4 in that set?

    JBZENT is the timestamp that the job entered the system, and JBZSTR is the timestamp that the job started. In all 3 cases (job number, entered_TS and started_TS) job JOB_4_1 should be listed first?

    Click image for larger version

Name:	job list 2.png
Views:	1
Size:	53.9 KB
ID:	148167
    Greg Craill: "Life's hard - Get a helmet !!"

    Comment

    sponsored links

    Collapse

    Working...
    X