June 23, 2014 at 12:38 am
Hi,
I am trying to put together a query which will tell me dependencies between jobs. I have a query which joins my schduled jobs to the jobs definition table. But what I really want to show, is a column for the name of its predecessor or successor. so far I have:
Select j.JOBNAME, , j.APPLICATION, j.SCHEDULE,
CASE
WHEN j.TASKTYPE = 'C' THEN j.CMDLINE
WHEN j.TASKTYPE = 'B' THEN j.MEMNAME
WHEN j.TASKTYPE = 'U' THEN NULL
END AS 'Task',
j.Start, j.Finish,
CASE
WHEN c.ROWTYPE ='O' THEN c.TaskStep
END AS OutputTask,
CASE
WHEN c.ROWTYPE ='I' THEN c.TaskStep
END AS InputTask
FROM JOBDEF j
JOIN CON_J c on c.JOBNO=j.JOBNO
But, I want to show the names of the jobs which have output tasks matching the in conditions in my reports, to show the dependencies, and chaining.
Not sure how to for this, or even if I have explained it very well! I am thinking that maybe a cte would be the way to go, but am struggling to get started.
Can anyone help please?
thank you
June 23, 2014 at 3:14 am
If you post some sample data & expected results you'll likely get more response.
You can use recursive CTEs to show hierarchies.
For example:
http://blogs.msdn.com/b/simonince/archive/2007/10/17/hierarchies-with-common-table-expressions.aspx
June 23, 2014 at 3:34 am
Tex-166085 (6/23/2014)
...I want to show the names of the jobs which have output tasks matching the in conditions in my reports...
Allthough sample data and desired output would help us much more in helping you, I will give it a shot:
First create a CTE with the jobs, names, output and input. Use this CTE in a SELECT and join the CTE with itself on OUTPUT = INPUT. It will look like:
;WITH CTE_Jobs AS (
SELECT job_id, name, input, output, .....
FROM FROM JOBDEF j
INNER JOIN CON_J c on c.JOBNO=j.JOBNO
WHERE ......
)
SELECT *
FROM CTE_Jobs Job_output
INNER JOIN CTE_Jobs Job_input
ON Job_output.output = Job_input.input
June 23, 2014 at 3:49 am
+1
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply