June 9, 2010 at 8:00 am
Hello,
I am not new to hierarchical data but I am new to CTE's and I think I am close if someone could just explain what I'm missing. Every tutorial out there shows how to write the CTE using one table but I'm trying to do a little more of a real world sample as follows:
Top Level Table in Sandbox Database([Sandbox].[dbo].[CoreBizProcesses])
BizProcessIDBizProcessNameBizProcessDesc
1001001 Main Process 1 NULL
1001002 Main Process 2 NULL
1001003 Main Process 3 NULL
1001004 Main Process 4 NULL
1001005 Main Process 5 NULL
1st Level Table In Sandbox Database ([Sandbox].[dbo].[1stLevelBizProcess])
BizProcessID 1stBizProcID 1stBizProcName 1stBizProcDesc 1stBizProcUrl
1001004 1001006 Website Space Filler N/A
1001003 1001007 Servicing Space Filler N/A
2nd Level Table in Sandbox ([Sandbox].[dbo].[2ndLevelBizProcess])
1stBizProcID 2ndBizProcID 2ndBizProcName 2ndBizProcDesc 2ndBizProcUrl
1001006 1001001 Chat Lenders ...Chat Lend... maint_chatlenders.aspx
1001007 1001002 Collections Overdue Collect. N/A
1001006 1001003 L Stat... L Stat & Desc N/A
1001006 1001004 IVR IVR Stuff N/A
1001007 1001005 Claims Claims Stuff ClaimsUrl
Now my CTE looks like this:
USE Sandbox
GO
WITH vw_FirstLevelBiz([BizProcessID], [BizProcessName], [BizProcessDesc], [1stBizProcID], [1stBizProcName], [1stBizProcDesc], [1stBizProcUrl])
AS
(
--Anchor query returning the top levels of the heirarchy
SELECT TopLvl.BizProcessID, TopLvl.BizProcessName, TopLvl.BizProcessName,
First.[1stBizProcID], First.[1stBizProcName], First.[1stBizProcDesc], First.[1stBizProcUrl]
FROM Sandbox.dbo.CoreBizProcesses AS TopLvl
INNER JOIN Sandbox.dbo.[1stLevelBizProcess] AS First
ON TopLvl.BizProcessID = First.BizProcessID
)
SELECT *
FROM vw_FirstLevelBiz;
Which Returns (Abbreviated column headers for space):
BizProcID BizProcName BizProcDesc 1BizProcID 1BizProcName 1BizProcDesc 1BizProcUrl
1001004 Main Proc4 Main Proc4 1001006 Website Space Filler N/A
1001003 Main Proc3 Main Proc3 1001007 Servicing Space Filler N/A
Main Process 3 & 4 are the only ones in my testing that have sub processes. Now this makes sense although it doesn't display as I would expect it to (What I mean is it displays exactly as I have it written but thought it would be more like the single table examples I've seen on the internet).
Here is where I get REALLY lost:
USE Sandbox
GO
WITH vw_FirstLevelBiz([BizProcessID], [BizProcessName], [BizProcessDesc], [1stBizProcID], [1stBizProcName], [1stBizProcDesc], [1stBizProcUrl])
AS
(
--Anchor query returning the top levels of the heirarchy
SELECT TopLvl.BizProcessID, TopLvl.BizProcessName, TopLvl.BizProcessName, First.[1stBizProcID], First.[1stBizProcName], First.[1stBizProcDesc], First.[1stBizProcUrl]
FROM Sandbox.dbo.CoreBizProcesses AS TopLvl
INNER JOIN Sandbox.dbo.[1stLevelBizProcess] AS First
ON TopLvl.BizProcessID = First.BizProcessID
--Code goes here for recursive queries but I'm not sure I understand how it knows who / what to recurse through???
)
SELECT *
FROM vw_FirstLevelBiz;
As you can see in the comments I know that my recursive code goes after the anchor, and I understand how it references itself but I don't understand how it knows who / what to query until it stops getting rows back or reaches the Recursive HINT.
So this is where I am stuck and any help or articles showing multi-table cte's would be greatly appreciated.
Thank You
June 9, 2010 at 8:14 am
Please write out your expected outcome, it's difficult to help from what you've displayed.
June 9, 2010 at 8:24 am
Hopefully I am explaining this in the correct way. What I am expecting is for my CTE to recurse through all three tables returning hierarchical data that I can then bind to a tree view etc.
Sorry for the OP not being explicit on this.
June 9, 2010 at 8:36 am
nfs_john (6/9/2010)
Hopefully I am explaining this in the correct way. What I am expecting is for my CTE to recurse through all three tables returning hierarchical data that I can then bind to a tree view etc.Sorry for the OP not being explicit on this.
Sorry, but no 🙂
Can you just type out the desired output from your the CTE? We can work out the answer, then work backwards with you until you understand.
While I'm at it, you should always try and give the sample data in a consumable way, so that people that decide to help can perform tests. For example, your three tables should've been coded like this: -
DECLARE @CoreBizProcesses AS TABLE(
[BizProcessID] INT,
[BizProcessName] VARCHAR(60),
[BizProcessDesc] VARCHAR(200)
)
INSERT @CoreBizProcesses ([BizProcessID], [BizProcessName], [BizProcessDesc])
VALUES (1001001,'Main Process 1',NULL);
INSERT @CoreBizProcesses ([BizProcessID], [BizProcessName], [BizProcessDesc])
VALUES (1001002,'Main Process 2',NULL);
INSERT @CoreBizProcesses ([BizProcessID], [BizProcessName], [BizProcessDesc])
VALUES (1001003,'Main Process 3',NULL);
INSERT @CoreBizProcesses ([BizProcessID], [BizProcessName], [BizProcessDesc])
VALUES (1001004,'Main Process 4',NULL);
INSERT @CoreBizProcesses ([BizProcessID], [BizProcessName], [BizProcessDesc])
VALUES (1001005,'Main Process 5',NULL);
DECLARE @1stLevelBizProcess AS TABLE(
[BizProcessID] INT,
[1stBizProcID] INT,
[1stBizProcName] VARCHAR(50),
[1stBizProcDesc] VARCHAR(50),
[1stBizProcUrl] VARCHAR(50)
)
INSERT @1stLevelBizProcess ([BizProcessID], [1stBizProcID], [1stBizProcName], [1stBizProcDesc], [1stBizProcUrl])
VALUES (1001004,1001006,'Website','Space Filler','N/A');
INSERT @1stLevelBizProcess ([BizProcessID], [1stBizProcID], [1stBizProcName], [1stBizProcDesc], [1stBizProcUrl])
VALUES (1001003,1001007,'Servicing','Space Filler','N/A');
DECLARE @2ndLevelBizProcess AS TABLE(
[1stBizProcID] INT,
[2ndBizProcID] INT,
[2ndBizProcName] VARCHAR(50),
[2ndBizProcDesc] VARCHAR(50),
[2ndBizProcUrl] VARCHAR(50)
)
INSERT @2ndLevelBizProcess ([1stBizProcID], [2ndBizProcID], [2ndBizProcName], [2ndBizProcDesc], [2ndBizProcUrl])
VALUES (1001006,1001001,'Chat Lenders','...Chat Lend...','maint_chatlenders.aspx');
INSERT @2ndLevelBizProcess ([1stBizProcID], [2ndBizProcID], [2ndBizProcName], [2ndBizProcDesc], [2ndBizProcUrl])
VALUES (1001007,1001002,'Collections','Overdue Collect.','N/A');
INSERT @2ndLevelBizProcess ([1stBizProcID], [2ndBizProcID], [2ndBizProcName], [2ndBizProcDesc], [2ndBizProcUrl])
VALUES (1001006,1001003,'L Stat...','L Stat & Desc','N/A');
INSERT @2ndLevelBizProcess ([1stBizProcID], [2ndBizProcID], [2ndBizProcName], [2ndBizProcDesc], [2ndBizProcUrl])
VALUES (1001006,1001004,'IVR','IVR Stuff','N/A');
INSERT @2ndLevelBizProcess ([1stBizProcID], [2ndBizProcID], [2ndBizProcName], [2ndBizProcDesc], [2ndBizProcUrl])
VALUES (1001007,1001005,'Claims','Claims Stuff','ClaimsUrl');
June 9, 2010 at 9:16 am
I surely can script out my tables etc. I figured you geniuses wouldn't need anything more than to see it to know where I am wrong.:-)
I was looking for something like this:
BizProc1
>N/A
BizProc2
>N/A
BizProc3
>Servicing
>Collections
>Claims
BizProc4
>Website
>Chat Lenders
>Loan Status
>IVR
BizProc5
>N/A
Now that is pseudo XML layout and can be bound to a tree control. Where I am going to frustrate you again is I don't know for sure what that should look like from a SQL RS standpoint. Something like this????
1BizID 2BizID 1BizProcName 2BizProcName 1BizProcDesc 1BizProcUrl
1001006 NULL Website NULL Space Filler N/A
NULL 1001001 NULL Chat Lenders Chat L's maint_....aspx
NULL 1001003 NULL Loan Status L Codes & Desc. N/A
NULL 1001004 NULL IVR IVR Stuff N/A
June 9, 2010 at 9:23 am
nfs_john (6/9/2010)
I surely can script out my tables etc. I figured you geniuses wouldn't need anything more than to see it to know where I am wrong.:-)
I think you're the only person that has ever called any of us a genuis... THANKS! :w00t:
Just remember that all of "us geniuses" do this as volunteers. We have a full-time job, and frequently we're swamped with our job. And most problems do require setting up a small environment to run things in to test out what will actually work (after all, we aren't really geniuses, so we need to play with it a bit first). So, we ask for data in this format. You will find that if you supply it, many more people will jump in to help you out... and if you don't, many people will skip right by your post.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 9, 2010 at 9:37 am
nfs_john (6/9/2010)
I surely can script out my tables etc. I figured you geniuses wouldn't need anything more than to see it to know where I am wrong.:-)
I figured you could, but as Wayne said, we're all volunteers here and are generally at work as we read these posts. Right now, I'm actually on a train heading to a meeting with a client, so time is often of the essence 😛
nfs_john (6/9/2010)
I was looking for something like this:
BizProc1
>N/A
BizProc2
>N/A
BizProc3
>Servicing
>Collections
>Claims
BizProc4
>Website
>Chat Lenders
>Loan Status
>IVR
BizProc5
>N/A
Now that is pseudo XML layout and can be bound to a tree control. Where I am going to frustrate you again is I don't know for sure what that should look like from a SQL RS standpoint. Something like this????
1BizID 2BizID 1BizProcName 2BizProcName 1BizProcDesc 1BizProcUrl
1001006 NULL Website NULL Space Filler N/A
NULL 1001001 NULL Chat Lenders Chat L's maint_....aspx
NULL 1001003 NULL Loan Status L Codes & Desc. N/A
NULL 1001004 NULL IVR IVR Stuff N/A
When I get access to a computer again, I'll take a look at scripting something out to produce this result.
-EDIT-
I've not forgotten about this. I struggled with it this morning and have since been working on projects at work. When I have some down time I'll have another look.
June 10, 2010 at 12:52 am
I guess, every recursive CTE must contain a primary anchor select, an UNION ALL and a secondary clause that will reference the name of the CTE.. recursion happens this way :
1. the anchor puls some records
2. The secondary will then read thro the pulled records and it will append the resutl set to the anchor
3. the newly added records will now become the anchor and then the secondary will process thro the new records..
I dint understand your original question, so i blabbed what knew.. Sorry if i mislead you!
June 10, 2010 at 9:52 am
Somehow I started this thread on the wrong foot so my original question was obscure. In the past building hierarchical data I had to use temp tables and go through other manipulations. I understand from my reading that CTE's are better on many fronts.
So I setup a "simulated" org structure with 3 tables and relations, keys, etc. I wrote a CTE from tutorials and wrote an anchor query. The next two parts I am unsure of. Writing a union and the reference to itself...how does it know to keep drilling down?
Perhaps my approach (top down) is wrong? Perhaps my anchor should be the bottom of the hierarchy and the recursion goes up? Perhaps my entire approach is bogus.
Every example on the internet builds a CTE from a single table which I assumed was for simplicity but maybee not. Perhaps my "real world" scenario needs to have a view that unions the three tables into one and then the CTE recurses the single "view table" the way the tutorials show??
June 10, 2010 at 2:52 pm
I could be missing something here ... I'll be honest, I didn't read through everything.
But I'm not sure on this where the cte recursion comes into play on this ... I'm not sure if I am not understanding what you're wanting, or if you just want to figure out how to do a recursive cte because you want to know, or if possibly it's just not needed.
Also, I haven't worked with the tree control ... do you actually want xml data for it or is it a proprietary format pseudo xml? Given your example of what you were wanting to return, this is what I did:
select cbp.[BizProcessName],
L1.[1stBizProcName],
L2.[2ndBizProcName]
from @CoreBizProcesses cbp
left join @1stLevelBizProcess L1
on L1.[BizProcessID] = cbp.[BizProcessID]
left join @2ndLevelBizProcess L2
on L2.[1stBizProcID] = L1.[1stBizProcID]
order by cbp.[BizProcessID], L1.[1stBizProcName], L2.[2ndBizProcName]
for xml auto
This returns:
<cbp BizProcessName="Main Process 1">
<L1>
<L2 />
</L1>
</cbp>
<cbp BizProcessName="Main Process 2">
<L1>
<L2 />
</L1>
</cbp>
<cbp BizProcessName="Main Process 3">
<L1 _x0031_stBizProcName="Servicing">
<L2 _x0032_ndBizProcName="Claims" />
<L2 _x0032_ndBizProcName="Collections" />
</L1>
</cbp>
<cbp BizProcessName="Main Process 4">
<L1 _x0031_stBizProcName="Website">
<L2 _x0032_ndBizProcName="Chat Lenders" />
<L2 _x0032_ndBizProcName="IVR" />
<L2 _x0032_ndBizProcName="L Stat..." />
</L1>
</cbp>
<cbp BizProcessName="Main Process 5">
<L1>
<L2 />
</L1>
</cbp>
Again, not sure if that's what you are looking for but I thought I would throw it out there since it's simple. Also, if you take out the 'for xml auto' you can see the flat version of the data.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply