SQL Script problem

[Freehansje]

I have this table

CREATE TABLE table1(
Proc VARCHAR(10),
PrevProc VARCHAR(10))

Both fields are mandatory. In this table sequences are stored of jobs to run. PrevProc will hold the name of the job which has to finish before the job in Proc can run. What I need is a resultset per sequence, which start with a jobname in PrevProc not existing in Proc(this is the first job of this specific sequence) and next all following jobnames in 12 row:

Job1, Job2, Job3, Job4 etc.

I have been fiddling with a SP, where I have played with temptables and such, but what I get is far from complete and very messy. I wonder if this should be possible to create in a view, but sofar I doubt that.

Any hints are welcome


Kind regards,
FreeHansje

 

[LNBruno]

Given that "Proc" is a reserved word, I changed the names a bit. Does this represent the data about which you're talking?

CREATE TABLE #JobSequence
   (
   JobName varchar(10)        NOT NULL
   , PrevJobName varchar(10)  NOT NULL
   )

INSERT INTO #JobSequence (JobName, PrevJobName) VALUES ('Job10', 'Job1')
INSERT INTO #JobSequence (JobName, PrevJobName) VALUES ('Job20', 'Job10')
INSERT INTO #JobSequence (JobName, PrevJobName) VALUES ('Job30', 'Job2')
INSERT INTO #JobSequence (JobName, PrevJobName) VALUES ('Job40', 'Job30')
INSERT INTO #JobSequence (JobName, PrevJobName) VALUES ('Job50', 'Job40')

If so, then I understand getting 'Job1' from PrevJobName, but, other than the associated 'Job10' from JobName, what would the resultset include?


< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill

 

[Freehansje]

Sorry, you are right, I should have given an example.
Suppose you have this sequence:
Job10=>Job30=>Job20 AND JOB25=>Job15
Translation: first Job10 needs to finish, then Job30, after that Job20 AND Job25 need to finish before lastly JOB15 can start. I hope I make myself clear.

 

[LNBruno]

Not really (I'm still working on my first cup of coffee, though, so it could just be me waking up...).

Which Job# is coming from which column? Both column contain a Job#. Using the table structure above, here's the data:

JobName	PrevJobName
Job10	Job1
Job20	Job10
Job30	Job2
Job40	Job30
Job50	Job40

I think what you're after is something like this?

PrevJobName - Job1, JobName - Job10
PrevJobName - Job10, JobName - Job20

Personally, I'd just add an INT column for the processing order like this (because I'm a lazy code monkey):

CREATE TABLE #JobSequence
   (
   JobOrder int        NOT NULL
   , JobName varchar(10)  NOT NULL
   )

INSERT INTO #JobSequence (JobOrder, JobName) VALUES (1, 'Job10')
INSERT INTO #JobSequence (JobOrder, JobName) VALUES (4, 'Job20')
INSERT INTO #JobSequence (JobOrder, JobName) VALUES (2, 'Job30')
INSERT INTO #JobSequence (JobOrder, JobName) VALUES (5, 'Job40')
INSERT INTO #JobSequence (JobOrder, JobName) VALUES (3, 'Job50')

SELECT * FROM #JobSequence
ORDER BY JobOrder

/*
--RESULTS:
JobOrder	JobName
========        =======
1	        Job10
2	        Job30
3	        Job50
4	        Job20
5	        Job40
*/

< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill

 

[Freehansje]

Hi LNBruno,
In the table you described the column Jobname holds the Job, which can only start after the job in column PrevJobname has ended. In your example 2 sequences would have to crop up:
1 Job1 => Job10 => Job20
2 Job2 => Job30 => Job40 => Job50

Now, these are single-item sequences, but you can imagine that for 1 job to start 2 other jobs would have to be finished first:

JobName PrevJobName
Job10 Job1
Job20 Job10
Job20 Job2

Now the sequence would be:

Job1 => Job10 AND Job2 => Job20

The simple table with 2 columns can handle this, but getting the sequences out requires backtracking. Since a sequence can theoretically be as long as you can imagine...In RL I am told a sequence will not be longer then 10 Jobs. But that is a user saying it...

 

[LNBruno]

...which is why I suggested just numbering the things in the sequence so you'd end up with a single job sequence:

Job1 => Job2 => Job10 => Job20 => Job30 => Job40 => Job50

I'm probably over-simplifying?

< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill

 

[Freehansje]

Yes, there are 2 job-sequences there; I need to get both those sequences. But I'm on the right track, I think. I was hoping there would be an easy, simple way of doing this, but I guess not.

Thnx,
FreeHansje

 

[ESquared]

Do a self-join and insert all jobIDs that have no predecessor to a worktable with three columns, StartingJobID, JobID, and Precedence. We'll call them S, J, and P.

if your source table has

[tt]Prevjobid Jobid
1 15
15 6
7 9
9 12
12 3[/tt]

then your new table will on the first insert have
[tt]S J P
1 1 1
7 7 1[/tt]

Now using a variable that counts from 1 upward, do a loop until no rows are inserted, joining back to your tables and inserting each next step. With my example it will run two times, finally yielding (new rows in bold):

[tt]S J P
1 1 1
1 15 2
7 7 1
7 9 2
7 12 3[/tt]

Then a final insert to pick up the second column in the source table:

[tt]S J P
1 1 1
1 15 2
1 6 3 -- max by startingjobid + 1
7 7 1
7 9 2
7 12 3
7 3 4[/tt]

Now perform a pivot

select startingjobid,
   job1 = max(case when Precedence = 1 then jobid else null end),
   job2 = max(case when Precedence = 2 then jobid else null end),
   job3 = max(case when Precedence = 3 then jobid else null end),
   job4 = max(case when Precedence = 4 then jobid else null end) -- repeat this to the max possible.
from
   worktable
group by
   startingjobid

But it's probably better to do it in the front end. Seriously.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[SQLSister]

If these things are all dependent on each other why not put them in the same job but in differnt steps?

"NOTHING is more important in a database than integrity." ESquared

 

[Freehansje]

If these things are all dependent on each other why not put them in the same job but in differnt steps?

Jobs maybe used in different sequences. Sequences may change over time, added and deleted. Re-usability is the idea behind this.

 

[ESquared]

So did my ideas help you at all?

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[Freehansje]

ESquared,
and all of you, yes you've helped, up to a point. I still am looking for a solution where 2 jobs(independend from each other) need to finish for a third job to start.
I'm working on 2 solutions, 1 of them with a different table, where some sequence information is stored as well.
I have not found out what best solution is.

TIA,
FreeHansje

 

[ESquared]

What result do you want when two jobs need to finish? What order do you want to see things in?

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[Freehansje]

Imagine this situation:
Job3 needs to start AFTER Job2 has finished AND Job4 has finished. Job2 can start AFTER Job7 has finished. Job7 and Job4 have no preceding job and can run independently from each other. It's difficult to explain here without graphic options, but I'll try. The sequence would be like this:

Job7 => Job2|
|=> Job3
Job4 => |

I hope I make myself clear. For a single sequence I can handle finding it in the given table. For this sequence, which is very common, I still need to find a good solution.

Greetz,
FreeHansje

 

[ESquared]

I already understood all that. What I'm asking is what resultset you want. You said in your first post:

What I need is a resultset per sequence, which start with a jobname in PrevProc not existing in Proc(this is the first job of this specific sequence) and next all following jobnames in 12 row:

And bam, look at that: I read that as 12 columns. Sigh. Sorry about that. At least my answer to you was good up to a point.

[tt]Job7 => Job2=\
|=> Job3
Job4 ========/[/tt]

P.S. you can force monospaced characters with the tag "tt" (helps with drawing diagrams):

[tt][ignore][tt][/ignore]monospaced text[ignore][/tt][/ignore][/tt]

So anyway, what rowset do you want now? Give a sample.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[Freehansje]

OK, here's a test example:

PrevJobname Jobname
Cluster_01 Cluster_02
Cluster_02 Cluster_04
Cluster_02 Cluster_08
Cluster_03 Cluster_04
Cluster_04 Cluster_05
Cluster_05 Cluster_06
Cluster_07 Cluster_08

There are 2 sequences here:
Cl_01=>Cl_02 AND Cl_03=>Cl_04=>Cl_05=>Cl_06

And
Cl_01=>Cl_02 AND Cl_07=>Cl_08

Now, you can see each of these examples as 2 sequences:
Cl_01=> Cl_02=>Cl_04=>Cl_05=>Cl_06
Cl_03=>Cl_04=>Cl_05=>Cl_06

And
Cl_01=> Cl_02=>Cl_08
Cl_07=>Cl_08

I can work with that, I think, but both examples are considered 1 sequence each! In the last 1 Cl_08 can only start after Cl_02 AND Cl_07 have finished, and Cl_02 can only start AFTER Cl_01 has finished.
I hope I make myself clear.
Ah, I'm very clumsy I think, can't figure out how to use the [tt][/tt] tag...

Greetz,
FreeHansje

 

[ESquared]

The things you put in your post are not rowsets. They're logical representations.

How do you want your rowsets to appear? Who will be using/consuming this information? What format do they expect it in? What are you actually trying to accomplish?

I think I/we have been so busy trying to answer your exact questions that we've failed to help you with your actual problem.

Please discuss the final usage of the information and what you need to actually do, and then we can help you accomplish it.

The easiest way that comes to mind if you're just trying to determine a job execution order is to start from the end and work backwards.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[Freehansje]

This set will be shown in a screen, in a Hierarchical Flexgrid to be exact. The cells in this Flexgrid will be stretched or merged where appropriate.
If I take example 2:
Cl_01=> Cl_02=>Cl_08
Cl_07=>Cl_08

Stretched: Cl_07 will be stretched to the width of Cl_01 and Cl_02. Merged: Cl_08 in both rows will be merged. It's difficult to explain without being able to use graphics!
The technique to do this is not a problem, I have been playing around with this. But to get the rowset from which to fill the Flexgrid... It's given me and a DBA headaches. We talked about changing the table to add sequence information, this is not acceptable to the designer of this screen, for reasons I can understand.

Greetz,
FreeHansje

 

[ESquared]

[tt]Cl_01 => Cl_02 =\
+ => Cl_08
[____Cl_07___] =/ [/tt]
Is this close to what you mean?

I haven't worked with hierarchical flexgrids. But is this basically like cells in Excel or in an HTML table, some of which can be merged, horizontally or vertically? In that case, you just need to specify the width and height of each cell.

But wait, I'm not sure that a hierarchy will work! I can imagine some networks of job dependencies which are pretty complicated. Each job can have multiple parents. What if several jobs share one set of parents, but also have their own independent parent? Eventually you get to the point where the diagram can't clearly show that a job is dependent on one parent and not another.

Take the 1, 2, 7, and 8 above. What if there was a second job 18 along with 8 that was dependent on these? And now what if there was another set of jobs, 11, 12, and 17, which were ALSO prerequisites for both 8 and 18. And finally, 18 and 8 each have one more jobs that is a prerequisite, 14 and 4 respectively. Now you can't have a hierarchy. What you've got now is a network, and a hierarchical flexgrid isn't suitable to the task.

In this diagram, see how C1_08 and C1_18 have conflicting parents and can't be grouped into a single cell:

[tt]Cl_01 => Cl_02 =\
+==> Cl_08, Cl_18
[____Cl_07___] =/ |
C1_04 =============> Cl_08
C1_18 =============> Cl_18
Cl_11 => Cl_12 =\
+==> Cl_08, Cl_18
[____Cl_17___] =/[/tt]

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[Freehansje]

Sorry for the delay, did not receive any notifications on this thread.
"Is this close to what you mean?"
Yes it is exactly right. A Flexgrid is comparable with cells in an Excelsheet.
On your other remarks, you might be right, I'll have to think on this and discuss it with the designer.

Tnx for your insights,
FreeHansje