December 1, 2016 at 4:54 am
SQL SERVER 2008 R2
As the subject implies, I wanted to know if I can create the cte query below as a View as opposed to an Table Function. I am not adverse to using a function I just didn't want to if there was another way, always trying to learn something new.
This is a simplified version of actual data sets and this is about Transact_SQL NOT SSIS
CREATE TABLE dbo.RunLog (RunLogID INT NOT NULL,LogLabel VARCHAR(50), ParentRunLogID INT NULL)
INSERT INTO dbo.RunLog (RunLogID,LogLabel, ParentRunLogID)
VALUES (1, 'Parent 1' ,NULL) -- 2 Issues
,(2, 'Parent 2', NULL) -- No Issues
,(3, 'Parent 3', NULL) -- No children, 3 Issues
,(4, 'Child 1 of Parent 1',1) -- No Issues
,(5, 'Child 2 of Parent 1',1) -- 4 Issues
,(6, 'Child 3 of Parent 1',1) -- No Grandchildren, No Issues
,(7, 'Child 1 of Parent 2',2) -- No Issues
,(8, 'Grandchild 1 of Child 1 Parent 1',4) -- 3 Issues
,(9, 'Grandchild 2 of Child 1 Parent 1',4) -- No Issues
,(10,'Grandchild 3 of Child 1 Parent 1',4) -- 2 Issues
,(11,'Grandchild 1 of Child 2 Parent 1',5) -- No Issue
,(12,'Grandchild 2 of Child 1 Parent 2',7) -- No Issues
CREATE TABLE dbo.RunLogIssues (IssueID INT NOT NULL,RunLogID INT NOT NULL)
INSERT INTO dbo.RunLogIssues (IssueID,RunLogID)
VALUES (1,1),(2,1),(3,3),(4,3),(5,3),(6,5),(7,5),(8,5),(9,5),(10,8),(11,8),(12,8),(13,10),(14,10)
DECLARE @runLogID INT = 5
;WITH cte_RunLogandChildren AS
(
-- Anchor
SELECT rl.RunLogID
,rl.LogLabel
,rl.ParentRunLogID
FROM dbo.RunLog rl
WHERE rl.ParentRunLogID IS NULL -- (All Parents All Children)
--WHERE rl.RunLogID = @runLogID -- (Specific Parent All Children)
UNION ALL
-- Children
SELECT rl.RunLogID
,rl.LogLabel
,rl.ParentRunLogID
FROM dbo.RunLog rl
JOIN cte_RunLogandChildren rlc ON rlc.RunLogID = rl.ParentRunLogID
)
SELECT rlc.RunLogID
,rlc.LogLabel
,rlc.ParentRunLogID
,SUM(CASE WHEN rli.IssueID IS NULL THEN 0 ELSE 1 END) AS RunIssueCount
FROM cte_RunLogandChildren rlc
LEFT JOIN dbo.RunLogIssues rli ON rli.RunLogID = rlc.RunLogID
-- WHERE rlc.RunLogID = @runLogID -- Only works on Top Level, any other level no children
GROUP BY rlc.RunLogID
,rlc.LogLabel
,rlc.ParentRunLogID
ORDER BY rlc.RunLogID,rlc.ParentRunLogID
The table called RunLogs contains 1 record per run, per SSIS package. All packages create a RunLog record with a unqiue RunLogID.
Besides creating a single run log record, a package might have issues, 0, 1 or N, which are logged in the table RunLogIssues. RunLogs has 0-N relationship to RunLogIssues via the RunLogIssues.RunLogID column
Any package may also run other packages - child packages. If a package is run by another it also creates a RunLogs record and possibly creates RunLogIssues records. It also records the Parent Run Log ID in the RunLog.ParentRunLogID column.
Since any package can be run by itself, it can either be a parent, a child, or neither ( isn't run by another package nor runs any package). It is only AFTER a package is run can it be determined whether it acted as child or parent or neither.
For reporting purposes I need to list a Run based on the RunLogID along with a count of issues. If there are children, grandchildren (and on and on) packages then I also need to include their issues in the count.
If I could include the Root RunLogID value in the final result I could filter on it when calling the view, but can't figure out a way to do that.
Thanks in advance for any and all comments
Steve
December 1, 2016 at 5:16 am
Steve
Great job on posting tables and sample data! Is this what you're looking for?WITH cte_RunLogandChildren AS
(
-- Anchor
SELECT rl.RunLogID
,rl.LogLabel
,rl.ParentRunLogID
,rl.RunLogID AS RootRunLogID
FROM dbo.RunLog rl
WHERE rl.ParentRunLogID IS NULL -- (All Parents All Children)
--WHERE rl.RunLogID = @runLogID -- (Specific Parent All Children)
UNION ALL
-- Children
SELECT rl.RunLogID
,rl.LogLabel
,rl.ParentRunLogID
,rlc.RootRunLogID
FROM dbo.RunLog rl
JOIN cte_RunLogandChildren rlc ON rlc.RunLogID = rl.ParentRunLogID
)
SELECT rlc.RunLogID
,rlc.LogLabel
,rlc.ParentRunLogID
,rlc.RootRunLogID
,SUM(CASE WHEN rli.IssueID IS NULL THEN 0 ELSE 1 END) AS RunIssueCount
FROM cte_RunLogandChildren rlc
LEFT JOIN dbo.RunLogIssues rli ON rli.RunLogID = rlc.RunLogID
-- WHERE rlc.RunLogID = @runLogID -- Only works on Top Level, any other level no children
GROUP BY rlc.RunLogID
,rlc.LogLabel
,rlc.ParentRunLogID
,rlc.RootRunLogID
ORDER BY rlc.RunLogID,rlc.ParentRunLogID
John
December 1, 2016 at 6:16 am
Heya John,
Thanks for the quick response. I have to take back my last statement...
If I could include the Root RunLogID value in the final result I could filter on it when calling the view, but can't figure out a way to do that.
... which is exactly what you provided but doesn't get me what I need and I think I already knew that. My bad.
Assuming that query was a view called RunIssueCounts I could do something like
SELECT RunLogID
,SUM(RunIssueCount) AS RunIssueCount
FROM vRunIssueCount
GROUP BY RunLogID
WHERE RootRunLogID = 1
Which is what I am hoping for but this query will only work with the ROOT, e.g RunLogID 1-4 (Doh! on me )
FWIW, i will add this to the final solution 🙂
I need this to work on any RunLogID, i.e. the count should always include itself and any children down the line.
Its this part of the query in the cte
WHERE rl.ParentRunLogID IS NULL -- (All Parents All Children)
If I change that to
DECLARE @runLogID INT = 3
...
WHERE rl.RunLogID = @runLogID -- (Specific Parent All Children)
I get some what I need.
Thanks
Steve
December 1, 2016 at 6:47 am
You might want something like this?
WITH cte_RunLogandChildren AS
(
-- Anchor
SELECT rl.RunLogID AS RootRunLogID
,rl.RunLogID
,rl.LogLabel
,rl.ParentRunLogID
FROM dbo.RunLog rl
WHERE rl.ParentRunLogID IS NULL -- (All Parents All Children)
-- WHERE rl.RunLogID = @runLogID -- (Specific Parent All Children)
UNION ALL
-- Children
SELECT rlc.RootRunLogID
,rl.RunLogID
,rl.LogLabel
,rl.ParentRunLogID
FROM dbo.RunLog rl
JOIN cte_RunLogandChildren rlc ON rlc.RunLogID = rl.ParentRunLogID
)
SELECT rlc.RootRunLogID AS RunLogID
,SUM(CASE WHEN rli.IssueID IS NULL THEN 0 ELSE 1 END) AS RunIssueCount
FROM cte_RunLogandChildren rlc
LEFT JOIN dbo.RunLogIssues rli ON rli.RunLogID = rlc.RunLogID
-- WHERE rlc.RunLogID = @runLogID -- Only works on Top Level, any other level no children
GROUP BY rlc.RootRunLogID
ORDER BY RunLogID;
December 1, 2016 at 7:58 am
Hi Luis,
Thanks for responding but similar to John's solution, since this is using the RootRunLogID, it doesn't allow me to filter on other RunLogs 5 thru 12, which I need to be able to do.
Cheers,
Steve
December 1, 2016 at 9:23 am
SteveD SQL (12/1/2016)
Hi Luis,Thanks for responding but similar to John's solution, since this is using the RootRunLogID, it doesn't allow me to filter on other RunLogs 5 thru 12, which I need to be able to do.
Cheers,
Steve
To define the root RunLogID, you need to use a function. It can be an inline table-valued function which would act like a view but will allow parameters.
December 1, 2016 at 9:42 am
So, if you're interested in RunLogID 5, you want all rows that have the same RootRunLogID (which is 1 for RunLogID 5)? Try this:DECLARE @runLogID INT = 5;
WITH cte_RunLogandChildren AS
(
-- Anchor
SELECT rl.RunLogID
,rl.LogLabel
,rl.ParentRunLogID
,rl.RunLogID AS RootRunLogID
FROM dbo.RunLog rl
WHERE rl.ParentRunLogID IS NULL -- (All Parents All Children)
--WHERE rl.RunLogID = @runLogID -- (Specific Parent All Children)
UNION ALL
-- Children
SELECT rl.RunLogID
,rl.LogLabel
,rl.ParentRunLogID
,rlc.RootRunLogID
FROM dbo.RunLog rl
JOIN cte_RunLogandChildren rlc ON rlc.RunLogID = rl.ParentRunLogID
)
SELECT rlc.RunLogID
,rlc.LogLabel
,rlc.ParentRunLogID
,rlc.RootRunLogID
,SUM(CASE WHEN rli.IssueID IS NULL THEN 0 ELSE 1 END) AS RunIssueCount
FROM cte_RunLogandChildren rlc
LEFT JOIN dbo.RunLogIssues rli ON rli.RunLogID = rlc.RunLogID
WHERE rlc.RootRunLogID = (
SELECT RootRunLogID
FROM cte_RunLogandChildren
WHERE RunLogID = @runLogID
)
GROUP BY rlc.RunLogID
,rlc.LogLabel
,rlc.ParentRunLogID
,rlc.RootRunLogID
ORDER BY rlc.RunLogID,rlc.ParentRunLogID
Alternatively, if you're really interested in hierarchies, get your grey matter round this[/url]. It'll blow your mind!
John
December 2, 2016 at 12:40 am
Heya John and Luis,
Thanks to you both for your input and ideas for improving what I had. It does seem to me, as Luis mentions, that I will need to use a inline table function, which is fine.
Thanks again to you both for your time.
Steve
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply