Ordering my data.. you would think this would be so simple...
-
SQL is great at getting data, it’s terribly awful at formatting data. I was hoping someone might be able to help me format my data a little. What I have are test cases that are broken into different “processes” a process can call another process right in the middle of execution. I’ve figured out how to get all of the steps from the different processes, the problem is when I list out the steps, they are out of order
Here’s the SQL statement that I use
WITH TestSteps (ProcessID, ExecProcessID, TestStepID, ComponentActionID, Narrative , CertifyValue, Level, CertifySequence, ComponentActionParmsID)
AS
(
-- Anchor member definition
SELECT TOP 100 PERCENT ts.ProcessID, tsa.ExecProcessID, ts.TestStepID, ts.ComponentActionID, ts.Narrative, tsa.CertifyValue, 1 as Level, ts.CertifySequence, tsa.ComponentActionParmsID
FROM TestStep as ts
JOIN TestStepAction as tsa
ON ts.TestStepID = tsa.TestStepID
WHERE ts.ProcessID=218--177
and tsa.ComponentActionParmsID <> 13
and tsa.ComponentActionParmsID <> 14
and tsa.ComponentActionParmsID <> 15
and tsa.ComponentActionParmsID <> 16
and tsa.ComponentActionParmsID <> 17 --AND ((tsa.ComponentActionParmsID)=12 Or (tsa.ComponentActionParmsID)=6)) --not tsa.ExecProcessID Is Null and ts.ComponentActionID = 11 and ProcessID = 218
and tsa.ComponentActionParmsID <> 1061
Order by ts.CertifySequence
UNION ALL
SELECT TOP 100 PERCENT ts.ProcessID, tsa.ExecProcessID, ts.TestStepID, ts.ComponentActionID, ts.Narrative, tsa.CertifyValue, Level + 1, ts.CertifySequence, tsa.ComponentActionParmsID
FROM TestStep as ts
JOIN TestStepAction as tsa
ON ts.TestStepID = tsa.TestStepID
Inner Join TestSteps as tst
ON ts.ProcessID = tst.ExecProcessID
WHERE tsa.ComponentActionParmsID <> 13
and tsa.ComponentActionParmsID <> 14
and tsa.ComponentActionParmsID <> 15
and tsa.ComponentActionParmsID <> 16
and tsa.ComponentActionParmsID <> 17
and tsa.ComponentActionParmsID <> 1061
order By ts.CertifySequence
)
-- Statement that executes the CTE
Select ProcessID, 'Step ' + LTrim(str(CertifySequence + 1)) + ': ', Narrative + char(13), level from testSteps --FOR XML PATH('')
After I execute this statement, I get this data (shown below) This is the order that the items are currently being displayed in.
ProcessID Sequence Process Name Level
218Step 1: Proj Process 1 Step 11
218Step 2: Proj Process 1 Step 2 (next step runs the OK Process to Execute) 1
218Step 3: Run Process OK Process to Execute1
218Step 4: Hello1
218Step 5: Load "www.google.com" 1
219Step 1: Ok Process to Execute Step 1 2
219Step 2: OK Process To Execute Step 2 (Next step launches Bad Process) 2
I would like to have the steps that are for the OK Process to Execute to be displayed directly under the process that was executed. The “Steps #:” display the sequence in the process they are ran. Numbers (1 and 2) in the last column indicate recursion level the SQL statement got those items from…
ProcessID Sequence ProcessName Level
218Step 1: Proj Process 1 Step 11
218Step 2: Proj Process 1 Step 2 (next step runs the OK Process to Execute)1
218Step 3: Run Process OK Process to Execute1
219Step 1: Ok Process to Execute Step 1 2
219Step 2: OK Process To Execute Step 2 (Next step launches Bad Process) 2
218Step 4: Hello1
218Step 5: Load "www.google.com" 1
Would you have any suggestions on what I could do to get these ordered like I have shown above? I have no other items in the database that can help with the sequence... If this method doesn't work... I may have to resort to using a cursor...
Aaron
-
Try ordering by a materialised path. Something like this
WITH TestSteps (ProcessID, ExecProcessID, TestStepID, ComponentActionID,Narrative , CertifyValue, Level, CertifySequence, ComponentActionParmsID,
FullPath)
AS
(
-- Anchor member definition
SELECT ts.ProcessID, tsa.ExecProcessID, ts.TestStepID, ts.ComponentActionID, ts.Narrative, tsa.CertifyValue, 1 as Level, ts.CertifySequence, tsa.ComponentActionParmsID,
CAST (ts.CertifySequence AS VARCHAR(MAX))
FROM TestStep as ts
JOIN TestStepAction as tsa
ON ts.TestStepID = tsa.TestStepID
WHERE ts.ProcessID=218--177
and tsa.ComponentActionParmsID <> 13
and tsa.ComponentActionParmsID <> 14
and tsa.ComponentActionParmsID <> 15
and tsa.ComponentActionParmsID <> 16
and tsa.ComponentActionParmsID <> 17 --AND ((tsa.ComponentActionParmsID)=12 Or (tsa.ComponentActionParmsID)=6)) --not tsa.ExecProcessID Is Null and ts.ComponentActionID = 11 and ProcessID = 218
and tsa.ComponentActionParmsID <> 1061
UNION ALL
SELECT ts.ProcessID, tsa.ExecProcessID, ts.TestStepID, ts.ComponentActionID, ts.Narrative, tsa.CertifyValue, Level + 1, ts.CertifySequence, tsa.ComponentActionParmsID,
tst.FullPath +'/' + CAST (ts.CertifySequence AS VARCHAR(MAX))
FROM TestStep as ts
JOIN TestStepAction as tsa
ON ts.TestStepID = tsa.TestStepID
Inner Join TestSteps as tst
ON ts.ProcessID = tst.ExecProcessID
WHERE tsa.ComponentActionParmsID <> 13
and tsa.ComponentActionParmsID <> 14
and tsa.ComponentActionParmsID <> 15
and tsa.ComponentActionParmsID <> 16
and tsa.ComponentActionParmsID <> 17
and tsa.ComponentActionParmsID <> 1061
)
-- Statement that executes the CTE
Select ProcessID, 'Step ' + LTrim(str(CertifySequence + 1)) + ': ', Narrative + char(13), level from testSteps --FOR XML PATH('')
order by FullPath
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
That worked! Thank you so very much!!! :w00t:
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply