How to get a variable resultset
-
I have this table
CREATE 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 shoudl be possible to create in a view.
Any hints are welcome
Greetz,
Hans Brouwer -
This is a hierarchy problem. This is pretty simple to solve with loops.
However one other solution I like is to have another design :
BatchID --ids the group of jobs to run
JobName --Name of the job to run
Sequence -- Order in which to run the jobs.
Once you add a unique constraint on BatchID, Sequence and another constraint on BatchID, JobName you will be sure to have valid data. Then the only trick is to make a small app that changes the order for ya. Takes about 1 hour to figure out and code.
Msg back if you need more help implementing this solution or if you need help implementing hierarchies (you can find a lot of solutions on this forum alone).
-
I think in this case you would have to check the entire dataset as the depth of the hierarchies will have to be decided dynamically ..... If this is the case you would have to loop through the database for (depth-1) times.
And i believe it will have to be traversed through by either a while loop or a cursor.
Please correct me if i am wrong ... i would be glad to know any other work around in this case.
-
Ninja,
You are right, a construction like that would make things easier. However, I am not in the position to change the table as it is presented. Also, the table as it is is datamodellingwise enough: it contains everythings you need. It's just more difficult to extract the info I need.
I guess then that I am on the right track, it's not possible to do this in 1 statement, i.e. a view. Too bad...
Greetz,
Hans Brouwer -
It's possible if you know the Max Depth of the tree... as you can do as many left joins as the max depth requires... but that also has some draw backs. This is one case where loops are pretty much the best solution.
-
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,
Hans Brouwer -
No it's very clear. You just need to search these forums for hierarchies and you'll find plenty of solutions for sql 2000. In 2005 the new solution is to use CTE.
-
You may want something like the following. If not you will need to post test data and expected results in order for the problem to be understood.
DECLARE @t TABLE
(
[Proc] varchar(10) NOT NULL
,PrevProc varchar(10) NULL
)
INSERT INTO @t
SELECT 'Job3', 'Job1' UNION ALL
SELECT 'Job1', 'Job2' UNION ALL
SELECT 'Job3', 'Job4' UNION ALL
SELECT 'Job2', 'Job7' UNION ALL
SELECT 'Job7', NULL UNION ALL
SELECT 'Job4', NULL
;WITH Results (ROrder, LevelProc)
AS
(
SELECT 1, [Proc]
FROM @t
WHERE PrevProc IS NULL
UNION ALL
SELECT R.ROrder + 1
,T.[Proc]
FROM Results R
JOIN @t T
ON R.LevelProc = T.PrevProc
)
SELECT *
FROM Results R
WHERE NOT EXISTS (
SELECT *
FROM Results R1
WHERE R1.LevelProc = R.LevelProc
AND R1.ROrder > R.ROrder
)
-
Ken,
Your code seems to do the trick. I'm going to test with real data, but the first few tests look very good. Thanks a lot!
EDIT:
Alas, when adding a few more sequences I receive a errormessage:Msg 530, Level 16, State 1, Line 15The statement terminated. The maximum recursion 100 has been exhausted before statement completion.Is there a recursion limit somewhere to be set? I run this currently in SQL2005, although it is going to be implemented on SQL2K...Forget about that, made a mistake. I have another problem now, I need to distinguish between different sequences. Suppose I have another sequence next to the previous 1:
Job5 =>Job6.
It needs to show up as a different sequence. Think I can figure this out myself, tho.
Tnx again.
Greetz,
Hans Brouwer -
Ken,
I need yopur help again, I think. Your script runs in SQL2005 and I need to have it run on SQL2K.
Also, I still have no solution for distinguishing between the different sequences...
Greetz,
Hans Brouwer -
In SQL2005, the best I can come up with for the sequence idea is a #temp table and two recursions. (As below)
If anyone can do any better I would be interested in knowing.
In SQL2000 you will have to use iteration. ie Create a #temp table for Results and populate it with a WHILE loop. There should be plenty of examples of doing this on the site.
CREATE TABLE #temp
(
ROrder int NOT NULL
,LevelProc varchar(10) COLLATE DATABASE_DEFAULT NULL
,PrevProc varchar(10) COLLATE DATABASE_DEFAULT NULL
,NextProc varchar(10) COLLATE DATABASE_DEFAULT NULL
)
-- *** Test Data ***
DECLARE @t TABLE
(
[Proc] varchar(10) NOT NULL
,PrevProc varchar(10) NULL
)
INSERT INTO @t
SELECT 'Job3', 'Job1' UNION ALL
SELECT 'Job1', 'Job2' UNION ALL
SELECT 'Job3', 'Job4' UNION ALL
SELECT 'Job2', 'Job7' UNION ALL
SELECT 'Job7', NULL UNION ALL
SELECT 'Job4', NULL UNION ALL
SELECT 'Job5', 'Job6' UNION ALL
SELECT 'Job6', NULL
-- *** End Test Data ***
;WITH Results (ROrder, LevelProc, PrevProc)
AS
(
SELECT 1
,[Proc]
,PrevProc
FROM @t
WHERE PrevProc IS NULL
UNION ALL
SELECT R.ROrder + 1
,T1.[Proc]
,T1.PrevProc
FROM Results R
JOIN @t T1
ON R.LevelProc = T1.PrevProc
)
INSERT INTO #temp
SELECT R.ROrder
,R.LevelProc
,R.PrevProc
,R1.LevelProc AS NextProc
FROM Results R
LEFT JOIN Results R1
ON R.LevelProc = R1.PrevProc
AND R.ROrder = R1.ROrder - 1
;WITH Results (Seq, ROrder, LevelProc)
AS
(
SELECT D.RowID, T.ROrder, T.LevelProc
FROM #temp T
JOIN (
SELECT ROW_NUMBER() OVER (ORDER BY D1.LevelProc) AS RowID
,D1.LevelProc
FROM (
SELECT DISTINCT T1.LevelProc
FROM #temp T1
WHERE T1.NextProc IS NULL
) D1
) D
ON T.LevelProc = D.LevelProc
UNION ALL
SELECT R.Seq, T2.ROrder, T2.LevelProc
FROM Results R
JOIN #temp T1
ON R.LevelProc = T1.LevelProc
AND R.ROrder = T1.ROrder
JOIN #temp T2
ON T1.PrevProc = T2.LevelProc
AND T1.ROrder = T2.ROrder + 1
)
SELECT Seq
,MAX(ROrder) AS ROrder
,LevelProc
FROM Results
GROUP BY Seq, LevelProc
ORDER BY Seq, ROrder
-
Ken,
If the purpose of the table is to hold 'precise' job sequences, then the fact that job3 is specified twice in your sample data seems to be incorrect. Thus there is no way for me to tell which goes first job1 or job4.
I think the data for this table comes from 'precise' job sequences, e.g. job2-job4-job7, job3-job1-job6, etc and is simply stored in this minimalist manner (a bit ridiculous in my opinion). Based on this, I think the values in column 'Proc' must be unique. Same goes for 'PrevProc' - but it can be NULL.
Maybe the OP can shed some light on where the data for this table comes from in the real world.
-
I tend to agree with you and Ninja has already pointed out to Hans that the data model is bad.
My understanding, based on the limited information provided, is that some procs can run in parallel while others have to wait until the previous proc completes. I was also assuming that a sequence is defined as a common end point. (eg Job3 and Job5) This could be completely wrong. I was just trying to use CTE's to get the desired results based on the information available.
-
I have just looked at this again and have managed to get nearly the same result with one CTE.
The difference is Job4 starts with Job1 and not Job7.
-- *** Test Data ***
DECLARE @t TABLE
(
[Proc] varchar(10) NOT NULL
,PrevProc varchar(10) NULL
)
INSERT INTO @t
SELECT 'Job3', 'Job1' UNION ALL
SELECT 'Job1', 'Job2' UNION ALL
SELECT 'Job3', 'Job4' UNION ALL
SELECT 'Job2', 'Job7' UNION ALL
SELECT 'Job7', NULL UNION ALL
SELECT 'Job4', NULL UNION ALL
SELECT 'Job5', 'Job6' UNION ALL
SELECT 'Job6', NULL
-- *** End Test Data ***
;WITH Results (Seq, ROrder, LevelProc, PrevProc)
AS
(
SELECT DENSE_RANK() OVER (ORDER BY T1.[Proc])
,1
,T1.[Proc]
,T1.PrevProc
FROM @t T1
LEFT JOIN @t T2
ON T1.[Proc] = T2.PrevProc
WHERE T2.PrevProc IS NULL
UNION ALL
SELECT R.Seq
,R.ROrder + 1
,T3.[Proc]
,T3.PrevProc
FROM Results R
JOIN @t T3
ON R.PrevProc = T3.[Proc]
)
SELECT D1.Seq
,D2.MaxROrder - D1.ROrder + 1 AS ROrder
,D1.LevelProc
FROM (
SELECT R1.Seq
,MIN(R1.ROrder) AS ROrder
,R1.LevelProc
FROM Results R1
GROUP BY R1.Seq, R1.LevelProc
) D1
JOIN (
SELECT R2.Seq
,MAX(R2.ROrder) AS MaxROrder
FROM Results R2
GROUP BY R2.Seq
) D2
ON D1.Seq = D2.Seq
ORDER BY Seq, ROrder
-
..is that some procs can run in parallel while others have to wait until the previous proc completes.
This is exactly the case.
I cannot test your example today, will get back tomorrow.Tnx for input all.I tested the code today and it looks like I can work with this set. Another problem is, that the script is to be used on different database platforms and needs to be as close to ANSI SQL as possible. I have grave difficulties to translate the specific SQL2005 functionality to ANSI SQL. Any1 can point me to info on how to translate those features?
Greetz,
Hans Brouwer
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply