March 13, 2014 at 1:32 pm
Hello --
I have a table that looks like this
ParentDeptCode - DeptCode
99 - 10
99 - 20
99 - 30
99 - 31
30 - 325
30 - 326
325 - 4152
325 - 4153
325 - 4154
Basically, parent DepCode of 99 is above DepCodes 10, 20, 30, 31.
DepCode 10 and 20 has no DepCodes under them.
DepCode 30 has DepCodes 325 and 326 under itself.
DepCode 325 has DepCodes 4152, 4153, 4154 under itself.
DepCode 326 has no DepCodes under itself.
(In other words, this is the hierarchy pyramid).
Is there a way to write a query that shows data in this way:
ParentDeptCode - DeptCode
99 - 10
99 - 20
99 - 30
99 - 31
99 - 325
99 - 326
99 - 4152
99 - 4153
99 - 4154
10 - 10
20 - 20
30 - 325
30 - 326
30 - 4152
30 - 4153
30 - 4154
325 - 4152
325 - 4153
325 - 4154
326 - 326
4151 - 4151
4152 - 4152
4153 - 4153
(Meaning show the ParentDepCode, and all the Depcodes that lie under it (directly, or indirectly)... and if a DepCode does not have any Depcodes under it, show the same DepCOde under both columns).
I can think of the cursor approach, but really don't like it, as this takes too much resources from SQL server.
Any help is appreciated.
March 13, 2014 at 2:04 pm
I'm not sure on how to create the order you need, but here's an option.
Be sure to understand it and test it because it might get slow when working with many hierarchy levels.
WITH SampleData( ParentDeptCode, DeptCode) AS( SELECT
99 , 10 UNION ALL SELECT
99 , 20 UNION ALL SELECT
99 , 30 UNION ALL SELECT
99 , 31 UNION ALL SELECT
30 , 325 UNION ALL SELECT
30 , 326 UNION ALL SELECT
325 , 4152 UNION ALL SELECT
325 , 4153 UNION ALL SELECT
325 , 4154
),
rCTE AS(
SELECT ParentDeptCode AS rootCode, *
FROM SampleData
UNION ALL
SELECT rootCode, s.*
FROM SampleData s
JOIN rCTE r ON s.ParentDeptCode = r.DeptCode
)
SELECT rootCode AS ParentDeptCode, DeptCode
FROM rCTE
UNION ALL
SELECT DeptCode AS ParentDeptCode, DeptCode
FROM SampleData s
WHERE NOT EXISTS( SELECT 1 FROM SampleData x WHERE s.DeptCode = x.ParentDeptCode)
ORDER BY rootcode, DeptCode
March 13, 2014 at 4:19 pm
Luis --
Thank you for your help!
Your code (when copied and pasted) runs, and does what I wanted to do, however when I try altering your code to qet the data from the table, I get an error message:
"Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion."
The code that gives me this error message is below:
----------------------------------------------------------------------
WITH SampleData( ParentDeptCode, DeptCode) AS(
SELECT HME_CNTL_POINT AS ParentDeptCode, HME_DEPT_NO AS DeptCode FROM AHVZHME_HME
),
rCTE AS(
SELECT ParentDeptCode AS rootCode, *
FROM SampleData
UNION ALL
SELECT rootCode, s.*
FROM SampleData s
JOIN rCTE r ON s.ParentDeptCode = r.DeptCode
)
SELECT rootCode AS ParentDeptCode, DeptCode
FROM rCTE
UNION ALL
SELECT DeptCode AS ParentDeptCode, DeptCode
FROM SampleData s
WHERE NOT EXISTS( SELECT 1 FROM SampleData x WHERE s.DeptCode = x.ParentDeptCode)
ORDER BY rootcode, DeptCode
---------------------------------------
I have uploaded the Excel file that holds all of the records from my table AHVZHME_HME
Any further help is really appreciated.
March 13, 2014 at 6:07 pm
I'm sorry, but I can't help you a lot right now. I'm busy at my job and can't examine carefully your information.
There are two possible reasons for this error. You might have too many levels which seems weird or you have a circular reference that will make the recursion to get into an infinite cycle and that's why SQL Server has that safety net.
Check your data, remove the following lines from the query:
UNION ALL
SELECT DeptCode AS ParentDeptCode, DeptCode
FROM SampleData s
WHERE NOT EXISTS( SELECT 1 FROM SampleData x WHERE s.DeptCode = x.ParentDeptCode)
And add this to your query:
WHERE rootCode = DeptCode
If you have any rows returned, you have circular references that need to be corrected.
March 13, 2014 at 6:30 pm
Luis --
Thanks again. I indeed had circular reference caused by the entry of (HME_CNTL_POINT = 200001 AND HME_DEPT_NO = 999999).
Once I excluded this entry, your solution totally worked.
Thanks again!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply