Pretty formatting options?

[gcraill]

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;

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.



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

[gcraill]

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?