How to get a variable resultset
-
I think I have got it nailed now tnx to a script found here:http://www.lazydba.com/sql/1__12293.htmlNow I cannot use the SQL, for the aplication it is used in can connect to either SQL Server or Oracle; have to translate this into VB...Tnx for all the help and input here.I was too quickly; this script still does not distinguish between different sequences starting with the same job...
Greetz,
Hans Brouwer -
Maybe the OP can shed some light on where the data for this table comes from in the real world.
This minimalistic way of storing data enables some piece of code somewhere to easily backtrack which jobs need to have run before Jobn can start. Some other similar processes exist and similar data is stored likewise as in the example given.
Is this a good way? No, it's messy. I have discussed this but it works somewhere else, we don't want it changed, we don't want to do it differently. Am I in a position to enforce a more effecient way? You guessed it: no I am not. It's even worse: all this has got to be done on the client side, in VB. Why? You guessed: we always do data manipulation this way, row-by-row on the client...
Greetz,
Hans Brouwer -
Hans, you need to be a bit clearer on this table and what exactly it holds.
Thus if I have job sequences:
job2 - job7
job2 - job1 - job4
job2 - job5 - job3 - job6
I have no problems writing a SS2K query on your table to get exactly that.
But if in this table you also have the job sequence:
job8 - job2 - job10
then everything falls to pieces.
The reason for this is that to determine a sequence the query I'm talking about needs to do hierarchical linking followed by checking that the first node is a start node and that the last node is an end node. In our case, job2 is all three, i.e. it's also an in-between node. Which means there is no solution to your problem.
So please provide a valid description of what this table can contain. Some real sample data speaks a thousand words.
-
OK, real data. The table is like this:
CREATE TABLE ClusterAfhankelijkheden(
Cluster varchar(10)
NextCluster Varchar(10)
)
Data:
Cluster NextCluster
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
A value in column Cluster, which cannot be found in the column NextCluster is the start of a sequence of jobs. In this example this is the case for Cluster_01, Cluster_03 and Cluster_07. Every 'job' in PrevCluster can start of the 'job' mentioned in Cluster has succesfully finished. The sequences in this table are:
1) Cluster_01=>Cluster_02=>cluster_04=>Cluster_05=>Cluster_06
2) Cluster_03=>Cluster_04=>Cluster_05=>Cluster_06
3) Cluster_01=>Cluster_02=>Cluster_08
4) Cluster_07=>Cluster_08
Sequences 1) and 2) ar considered 2 parts of the same batch of jobs. As you can see Cluster_04 is dependant on BOTH Cluster_02 AND Cluster_03 to be finished. To clarify more, you need to read sequences 1) and 2) like this:
Cluster_06 starts AFTER Cluster_05 has finished; Cluster_05 starts AFTER Cluster_04 has finished; Cluster_04 starts AFTER Cluster_03 AND Cluster_02 has finished; Cluster_02 starts AFTER Cluster_01 has finished. Cluster_01 and Cluster_03 are independent and can run simultaneously.
The same for Sequences 3) and 4):
Cluster_08 starts AFTER Cluster_02 AND Cluster_07 have finished; Cluster_02 starts AFTER Cluster_01 has finished. Cluster_01 and Cluster_07 are independent and can run simultaneously.
As you can see in those examples Cluster_01 is the start of 2 DIFFERENT sequences, seqnr 1 and 3.
The aim of the apl is to enable to view sequences, edit those sequences and create new sequences. The actual processing of the jobs is outside the scope of this apl. My main issue right now is to get a resultset which gives me these sequences. After that I need to manipulate them to get them into a Flexgrid and manipulate this Flexgrid to get a layout desired by the designer; I can do that, this is not a problem, retrieving the resultset is the problem.
I hope I have made myself somewhat clearer. If not, let me know. Please realize, that the table ClusterAfhankelijkheden can not be changed. I know another layout with Sequence info would be better, no chance of changing this.
Greetz,
Hans Brouwer -
You have decided to switch to NextCluster instead of PrevCluster. I prefer NextCluster because I tend to read from left to right
.Doing a hieararchical query typically is based on some reasonable limit of the length of a sequence. What is the maximum length of a sequence you would expect in you case?
The efficiency of a hierarchical query depends on the number of rows in the table. These typically represent company hierarchies (or similar stuff) and are thus fairly contained. In your case, who many rows would you typically find in this table?
-
As I said, this approach is valid only if the length of a sequence is within a reasonable limit and the number of rows in the table is reasonable. You should be able to see the pattern and generate the remaing blocks depending on the maximum length of a sequence you expect.
create table Cluster(
Cluster varchar(10),
NextCluster varchar(10))
insert into Cluster values('Cluster_01','Cluster_02')
insert into Cluster values('Cluster_02','Cluster_04')
insert into Cluster values('Cluster_02','Cluster_08')
insert into Cluster values('Cluster_03','Cluster_04')
insert into Cluster values('Cluster_04','Cluster_05')
insert into Cluster values('Cluster_05','Cluster_06')
insert into Cluster values('Cluster_07','Cluster_08')
select Cluster,NextCluster,'-','-','-'
from Cluster c
where c.Cluster not in (select NextCluster from Cluster)
and c.NextCluster not in (select Cluster from Cluster)
union
select c1.Cluster,c1.NextCluster,c2.NextCluster,'-','-'
from Cluster c1,Cluster c2
where c1.NextCluster=c2.Cluster
and c1.Cluster not in (select NextCluster from Cluster)
and c2.NextCluster not in (select Cluster from Cluster)
union
select c1.Cluster,c1.NextCluster,c2.NextCluster,c3.NextCluster,'-'
from Cluster c1, Cluster c2, Cluster c3
where c1.NextCluster=c2.Cluster
and c2.NextCluster=c3.Cluster
and c1.Cluster not in (select NextCluster from Cluster)
and c3.NextCluster not in (select Cluster from Cluster)
union
select c1.Cluster,c1.NextCluster,c2.NextCluster,c3.NextCluster,c4.NextCluster
from Cluster c1, Cluster c2, Cluster c3, Cluster c4
where c1.NextCluster=c2.Cluster
and c2.NextCluster=c3.Cluster
and c3.NextCluster=c4.Cluster
and c1.Cluster not in (select NextCluster from Cluster)
and c4.NextCluster not in (select Cluster from Cluster)
-
Hi Michael,
I'll have a look at your code tomorrow, it looks good. The max length would be not more then 10, but that's a user talking...
Tnx for the help sofar.
Greetz,
Hans Brouwer -
Hi Michael,
I've done some testing and sofar your code seems to be the thing I need. Thanks a lot!
Greetz,
Hans Brouwer -
Great.
But 10 levels is going to be a lot.
I have also wondered how this table is managed - especially in regards to deleting 'things' from that table. Deleting one sequence will cut off the tail of another. But I suppose the sequence is not what counts.
-
When a a chain from a sequence is removed 2 new sequences appear.
Cl_01=>Cl_02=>Cl_03
If the middle chain Cl_02 is removed 2 the new sequences are Cl_01 and Cl_03. Ofcourse, single-sequences are no sequences, but you get the drift. Managing the sequences is what the apl I'm working on is supposed to be doing. The table structure is indeed very simple, 2 columns.
Maybe 10 is long, but I have to take this into account. Maybe the designer can be convinced to take another approach (like storing sequence information in a table) if I can show what a nuisance retrieving sequences is...
Greetz,
Hans Brouwer -
Yet another question on this: storing sequence infortmation is not an option. Is there an easy way to find the longest existing sequence before creating the query Michael has provided? This will tell me how many unions are needed.
Greetz,
Hans Brouwer -
If you don't store sequence info - how are you going to prevent infinite loops? With what you're advocating - it seems to be only a matter of time before someone inserts a new record, and your procedures grinds your server into a pile of molten plastic.
J1-->J2
J2-->J3
J2-->J15
.....
J15-->J1
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Matt,
You are correct, but this is easily checked when creating a sequence. No sequences currently exists and won't be when this apl goes live. Sequences have to be made by this apl. If the check mechanism is correct no circle connection could be made.
Greetz,
Hans Brouwer -
The reason I am asking is that if you don't know how deep this will go - you need a recursive method, which would very easily subject you to any infinite loops it runs into. And - the recursive method is painful enough that you might as well build the sequence iteratively (I'm tempted to use the word recursive - but the method I describe is ACTUALLY implemented iteratively using a WHILE loop).
The UNION method although neat will NOT scale (and I don't know about your users, but if they say "no more than 10", that means "eventually will be a LOT more than 10, perhaps 20 or more")
If you're going to go that way into it - you'll be better off using the iteration to BUILD the sequences for you and use some type of PIVOT operation to flatten it out.
Something roughly like this:
TRUNCATE TABLE workingtable
Insert workingtable (seqnum,seqdepth,cluster,nextcluster)
select @@row_number, 0,cluster, nextcluster from CLUSTERSEQ where cluster not in (select nextcluster from clusterseq)
set @rows=1
set @x=1
While @row>0
BEGIN
insert workingtable (seqnum,seqdepth,cluster,nextcluster)
select wt_out.seqnum, @x,c.cluster, c.nextcluster from CLUSTERSEQ c
inner join workingtable wt_out on wt_out.nextcluster=c.cluster
where wt_out.seqdepth=@x-1 and
c.cluster not in (select cluster from workingtable wt_in where wt_out.seqnum=wt_in.seqnum)
set @rows=@@ROWCOUNT
set @x=@x+1
END
Once you build this way - the results you want are simply a PIVOT operation
SELECT seqnum,
[0], [1], [2], [3], [4],............................... --{however many that is - could go quite high)
FROM
(SELECT seqnum, seqdepth,cluster
FROM CLUSTERSEQ) AS SourceTable
PIVOT
(
Max(cluster)
FOR seqdepth IN ([0], [1], [2], [3], [4],...........)
) AS PivotTable
Keep in mind you could easily put this piece together as dynamic SQL using the ending value of x, or just put some ludricous number of numbers in the pivot statement.
That is some messy process logic you got - be careful with it
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Tnx for answering Matt.
However, at first sight thye code does not work: all records end up with NULL as seqnum and 0 as seqdepth. I'll have a look at the code during the weekend.
Tnx again,
Greetz,
Hans Brouwer
Viewing 15 posts - 16 through 30 (of 42 total)
You must be logged in to reply to this topic. Login to reply