| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
03-12-10, 09:01
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 9
|
|
|
Newbie question on Recursive CTE
|
|
I am trying to return data in a hierarchy.
Below is the data for 1 root level folder, the subfolders for that item (same project table), and some documents in those 1st level subfolders.
select * from dbo.project where projname = 'SFDS'
Code:
ProjID ProjPID ProjName ProjOwner ProjTree
370606 NULL SFDS DMA 370606
select * dbo.project where projpid = '370606.0'
Code:
ProjID ProjPID ProjName ProjOwner ProjTree
586655.0 370606.0 Manage WSA 370606
493205.0 370606.0 test TSH 370606
448003.0 370606.0 *TrashCan TNO 370606
437203.0 370606.0 Letters WSA 370606
432986.0 370606.0 *SFDS DMA 370606
425843.0 370606.0 _Index DMA 370606
420140.0 370606.0 *Removed WSA 370606
402924.0 370606.0 Confid WSA 370606
376760.0 370606.0 *Duplicate DMA 370606
374135.0 370606.0 Technology DMA 370606
select * from dbo.project_content where projtree = '370606'
Code:
SID ProjID Item_ID ProjTree
370606 64364.0 4886791.0 370606
select * from dbo.document where docnum = '4886791'
Code:
DocNum DocName DocOwner EditDate CreateDate
4886791.0 0042.tif SBL 2003-07-28 14:07:30.000 2003-12-16 17:29:36.000
but my query;
Code:
with projectdocs as
(
select a.projname as [Root],
b.projname as [SubFolder],
c.item_id as [Docnum],
d.docname as [Document Name]
from dbo.document d
inner join dbo.project_content c
on d.docnum = c.item_id
inner join dbo.project b
on b.projid = c.projid
inner join dbo.project a
on a.tree_id = c.tree_id
union all
select e.prj_name as [Root],
f.projname as [SubFolder],
g.item_id as [Docnum],
h.docname as [Document Name]
from dbo.document h
inner join dbo.project_content g
on h.docnum = g.item_id
inner join dbo.project f
on f.projid = g.projid
inner join dbo.project e
on e.tree_id = g.tree_id
)
select * from projectdocs
where root = '36076'
returns 0 results.
I don't understand where I've gone wrong!? Can someone please help me understand this? Thank you.
|
|
03-12-10, 09:56
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
First of all, I think you should move tables that are irrelevant to the hierarchy (project_content and document in your case) outside the recursive CTE, if only for simplicity sake. Then, to be recursive the CTE should refer to itself, that is, to projectdocs, which you don't do.
In other words, your query should look something like
Code:
with hierarchy as (
select .. from project where..
union all
select .. from project p, hierarchy h
where p.parent_id = h.child_id ...
) hierarchy
select ...
from hierarchy, project_content, document
where ...
|
|
03-12-10, 12:42
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 9
|
|
|
|
Thank you for responding to my request!
I have modified my query to the following;
Code:
with hierarchy as (
select projid, projname, projpid from dbo.project
union all
select p.projid, p.projname, h.projpid from dbo.project p, hierarchy h
where p.projid = h.projpid
) select *
from hierarchy
where projid = '592151.0'
Which nows returns the following error;
Code:
Server: Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
This projid has 1 subfolder and 1 document, so i'm unsure how to interpret a recursion level of 100??
|
|
03-12-10, 13:44
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
Of course, you need to stop recursion somewhere. Here's one of the ways:
Code:
with hierarchy as (
select projid, projname, projpid, 1 as level from dbo.project
union all
select p.projid, p.projname, h.projpid, h.level + 1 from dbo.project p, hierarchy h
where p.projid = h.projpid and h.level < 100
) select *
from hierarchy
where projid = '592151.0'
|
|
03-12-10, 18:07
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 9
|
|
Quote:
Originally Posted by n_i
Of course, you need to stop recursion somewhere. Here's one of the ways:
Code:
with hierarchy as (
select projid, projname, projpid, 1 as level from dbo.project
union all
select p.projid, p.projname, h.projpid, h.level + 1 from dbo.project p, hierarchy h
where p.projid = h.projpid and h.level < 100
) select *
from hierarchy
where projid = '592151.0'
|
Thanks again for your help!
This query returns to me 100 records, all the same;
Code:
ProjID ProjName ProjPID Level
529151 Standard Form NULL 1
529151 Standard Form 592151 2
529151 Standard Form 592151 3
529151 Standard Form 592151 4
529151 Standard Form 592151 5
and so on.....
Now, I substituted projid for projPID in my where clause and it returned the following;
Code:
ProjID ProjName ProjPID Level
592152 Documents 592151 1
592151 Standard Form 592151 2
592151 Standard Form 592151 3
592151 Standard Form 592151 4
592151 Standard Form 592151 5
I think I understand why the 2nd query returned the correct subfolder - but it seems the hierarchy stopped there. It did not return the child-object to ProjID = 592152.
Why???
|
Last edited by brettdmd; 03-12-10 at 18:10.
|
03-14-10, 16:05
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 9
|
|
I am sorry, but I am still struggling with this.
Here is a query which returns the correct data, in the expected format - but only for those records where sub.projpid = root.projid;
Code:
select root.projname as [Root Folder],
sub.projname as [Sub Folder],
docs.docname as [Documents]
from dbo.project root
inner join dbo.project sub
on root.projid = sub.projpid
left join dbo.project_content items
on items.projid = sub.projid
left join dbo.content docs
on items.item_id = docs.docnum
where root.projid = 370606.0
order by sub.projname asc
returns;
Code:
Root Folder Sub Folder Documents
Root Folder 1 Child Subfolder 1 Document
Root Folder 1 Child Subfolder 1 Document
Root Folder 1 Child Subfolder 2 Document
Root Folder 1 Child Subfolder 2 Document
Root Folder 1 Child Subfolder 3 Document
Root Folder 1 Child Subfolder 4 Document
Where I break down is trying to map this query to a recursive query, so that my output actually looks like this;
Code:
Root Folder Sub Folder Child Documents Sub-Sub Folder Grandchild Documents Sub-Sub-Sub Folder Great-GrandChild Documents
Root Folder 1 Child Subfolder Document NULL NULL NULL NULL
Root Folder 1 Child Subfolder Document NULL NULL NULL NULL
Root Folder 2 Child Subfolder NULL Grandchild Subfolder Document NULL NULL
Root Folder 2 Child Subfolder NULL Grandchild Subfolder Document NULL NULL
Root Folder 3 Child Subfolder Document Grandchild Subfolder Document NULL NULL
Root Folder 4 Child Subfolder NULL Grandchild Subfolder NULL GrtGrandchild Subfolder Document
With the appearance of NULL indicating no folder, or no folder content.
I'm almost ready to believe this just isn't possible 
|
|
03-15-10, 09:08
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
Please provide a consistent example of your data, along with the desired output (based on that example).
|
|
03-15-10, 10:01
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 9
|
|
Quote:
Originally Posted by n_i
Please provide a consistent example of your data, along with the desired output (based on that example).
|
Thank you so much n_i for your ongoing help with this!!!
select top 10 projid, projpid, projname, projowner, projtree from dbo.project
where projtree = 370606
Code:
projid projpid projname projowner projtree
370606 NULL SFDS DMA
586655 370606 Manage WSA 370606
586656 586655 Account WSA 370606
586657 586656 Working WSA 370606
493205 370606 test TSH 370606
448003 370606 *Trash TNO 370606
437203 370606 Engage WSA 370606
421155 437203 Letters WSA 370606
432986 370606 Change Log DMA 370606
425843 370606 _Index DMA 370606
select top 10 * from dbo.project_content
where projid in (select projid from dbo.project where projtree = 370606)
Code:
SID Parent_SID PROJID ITEM_ID Tree_ID
4693592 NULL 448003 8152951 4693592
4367070 NULL 448003 8937803 4367070
4367069 NULL 448003 8937804 4367069
3835998 NULL 381060 8207328 3835998
4446356 NULL 381060 9026460 4446356
4863678 NULL 381060 9384431 4863678
3998436 NULL 420140 8587893 3998436
3998442 NULL 420140 8587910 3998442
4366230 NULL 420140 8947726 4366230
4013232 NULL 421155 8542876 4013232
select * from dbo.project_content
where projid in (select projid from dbo.project
where tree_id = '370606')
Code:
DocNum DocName DocOwner EditDate CreateDate
8152951 Template DMA 2007-11-15 01:40:16.000 2007-11-15 01:40:10.000
8207328 Standard Form DMA 2007-12-05 20:59:36.000 2007-12-05 14:10:13.000
8542876 Dec. 11/07 JCT 2008-05-07 20:51:02.000 2008-05-06 14:23:20.000
8587893 REMOVED DOCS TNO 2008-05-22 20:26:21.000 2008-05-22 20:26:21.000
8587910 Docs Removed TNO 2008-05-22 20:34:07.000 2008-05-22 20:34:07.000
8937803 Agr No. 1 LMC 2008-09-29 13:54:05.000 2008-09-29 13:54:05.000
8937804 Agr No. 2 LMC 2008-09-29 13:54:20.000 2008-09-29 13:54:20.000
8947726 SFDS EEL 2008-10-02 19:21:58.000 2008-10-02 19:21:58.000
9026460 RefCard MCR 2008-10-31 12:42:07.000 2008-10-31 12:39:13.000
9384431 2007 Index EEL 2009-03-13 19:15:15.000 2009-03-13 19:15:15.000
Desired output on this example would be;
Code:
Root Folder Subfolder Sub-SubFolder Document Created Date Edit Date
SFDS Manage NULL NULL NULL NULL NULL
SFDS test NULL NULL NULL NULL NULL
SFDS _Index UserDocs NULL RefCard (2009) 2008-01-02 18:48:35.000 2008-01-02 18:48:35.000
SFDS _Index Audit NULL Audit Form 2005-07-01 18:48:35.000 2008-01-02 18:48:35.000
SFDS Change Log Log NULL NULL 2003-01-23 13:13:47.000 2009-01-14 19:04:49.000
SFDS Engage NULL NULL NULL NULL NULL
SFDS *Trash Template NULL NULL 2007-11-15 01:40:10.000 2007-11-15 01:40:16.000
SFDS *Trash Agr No. 1 NULL NULL 2008-09-29 13:54:05.000 2008-09-29 13:54:05.000
SFDS *Trash Agr No. 2 NULL NULL 2008-09-29 13:54:20.000 2008-09-29 13:54:20.000
SFDS Forms 2009 Corp Ontario CSP 2009-01-03 12:42:18.000 2009-10-11 14:12:12:000
SFDS Bids NULL NULL NULL NULL NULL
|
Last edited by brettdmd; 03-15-10 at 10:56.
|
03-15-10, 10:47
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
Sorry, I cannot help you since you keep showing me some made-up data that don't make sense.
This cannot be:
Quote:
Originally Posted by brettdmd
select top 10 projid, projpid, projname, projowner, projtree from dbo.project
where projtree = 370606
Code:
projid projpid projname projowner projtree
370606 NULL SFDS DMA
586655 370606 Manage WSA 370606
|
This cannot be either:
Quote:
Originally Posted by brettdmd
select * from dbo.project_content
where projid in (select projid from dbo.project
where tree_id = '370606')
Code:
DocNum DocName DocOwner EditDate CreateDate
|
The recursive SQL example that I have given earlier should work, if you put the right project ID in the WHERE clause in the right place. Once you have the hierarchy, you can figure out how to pivot it to your liking.
|
|
03-15-10, 10:52
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 9
|
|
Quote:
Originally Posted by n_i
Sorry, I cannot help you since you keep showing me some made-up data that don't make sense.
This cannot be:
This cannot be either:
The recursive SQL example that I have given earlier should work, if you put the right project ID in the WHERE clause in the right place. Once you have the hierarchy, you can figure out how to pivot it to your liking.
|
I am sorry, but the data is not made up. This is how it appears in the db.
The first example you've highlighted shows a root level folder, and a subfolder.
Record #1 is the parent folder to Record #2, as indicated by the relationship between PRJID, PRJPID and TREE_ID.
I suppose this solution is simply not possible, given that all parent/child/grandchild objects are referenced within the same table, dbo.project?
dbo.project_content simply indicates which documents are referenced within an associated parent-folder.
Many thanks for your assistance with this, but I guess it just cannot be done.
|
|
03-15-10, 11:39
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
Quote:
Originally Posted by brettdmd
The first example you've highlighted shows a root level folder, and a subfolder.
|
Can you please explain then how projtree can be at the same time 370606 (in the WHERE clause) and blank (in the presumed query output)?
|
|
03-15-10, 11:49
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 9
|
|
Quote:
Originally Posted by n_i
Can you please explain then how projtree can be at the same time 370606 (in the WHERE clause) and blank (in the presumed query output)?
|
You are correct, it should appear there. Not sure why it didn't copy over with the rest of my result-set. That is my mistake, and I'm sorry for the mixup.
|
Last edited by brettdmd; 03-15-10 at 12:14.
|
03-15-10, 12:55
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
OK, let's continue. How come "select top 10 * from dbo.project_content" and "select * from dbo.project_content" return not just different result sets, but different columns? How do you explain that "select * from dbo.project_content where projid in (something)" does not return the column "projid"?
Anyway, I positively refuse to participate in this mindreading session unless you copy/paste the queries and their exact output.
|
|
03-15-10, 14:06
|
|
Registered User
|
|
Join Date: Mar 2010
Posts: 9
|
|
Quote:
Originally Posted by n_i
OK, let's continue. How come "select top 10 * from dbo.project_content" and "select * from dbo.project_content" return not just different result sets, but different columns? How do you explain that "select * from dbo.project_content where projid in (something)" does not return the column "projid"?
Anyway, I positively refuse to participate in this mindreading session unless you copy/paste the queries and their exact output.
|
I am sorry, and not trying to be deceiving or difficult. The columns in the sample codes do not align correctly and the errors you've identified must be the result of me trying to adjust the column layout. In trying to make it perfect, I've obviously made it much worse.
Anyway, here is the exact output from these queries;
select top 10 * from dbo.project where projtree = 370606
Code:
PROJID PROJPID DEFAULT_SECURITY IS_SECURED PROJNAME PROJOWNER PROJDESCRIPT PROJSTATE PROJPUBLIC PROJLOCATION TYPE SUBTYPE INHERITS_SECURITY DOCNUM VERSION CUSTOM1 CUSTOM2 CUSTOM3 LEFT_VISIT RIGHT_VISIT TREE_ID EMAIL DOC_SAVED_SEARCH LAYOUT_NAME LAYOUT_ORDER LAYOUT_VIEW REFERENCE_DATABASE REFERENCE_PROJID REFERENCE_TYPE REFERENCE_SUBTYPE IS_EXTERNAL EXTRNL_AS_NRML IS_DOC_SVD_SRCH IS_PRJ_SVD_SRCH IS_HIDDEN EDITWHEN
370606 NULL V 1 SFDS DMA NULL NULL N NULL 1 work N 8103579 1 NULL NULL NULL 1 410 370606 NULL NULL NULL 0 NULL NULL NULL NULL NULL N N N N N 2010-01-29 19:05:51.000
586655 370606 I 0 Manage WSA NULL NULL N NULL 0 NULL N NULL NULL NULL NULL NULL 2 7 370606 NULL NULL NULL 0 NULL NULL NULL NULL NULL N N N N N 2010-01-29 19:05:51.000
586656 586655 I 0 Account WSA NULL NULL N NULL 0 NULL N NULL NULL NULL NULL NULL 3 6 370606 NULL NULL NULL 0 NULL NULL NULL NULL NULL N N N N N 2010-01-29 19:05:51.000
586657 586656 I 0 Docs WSA NULL NULL N NULL 0 NULL N NULL NULL NULL NULL NULL 4 5 370606 NULL NULL NULL 0 NULL NULL NULL NULL NULL N N N N N 2010-01-29 19:05:51.000
493205 370606 I 0 test TSH NULL NULL N NULL 0 Q N NULL NULL NULL NULL NULL 8 9 370606 NULL NULL NULL 0 NULL NULL NULL NULL NULL N N N N N 2010-01-29 19:05:51.000
448003 370606 X 1 *Trash TNO NULL NULL N NULL 0 NULL N NULL NULL NULL NULL NULL 10 11 370606 NULL NULL NULL 0 NULL NULL NULL NULL NULL N N N N N 2010-01-29 19:05:51.000
437203 370606 I 0 Engage WSA NULL NULL N NULL 0 Q N NULL NULL NULL NULL NULL 12 15 370606 NULL NULL NULL 0 NULL NULL NULL NULL NULL N N N N N 2010-01-29 19:05:51.000
421155 437203 I 0 Letters WSA NULL NULL N NULL 0 NULL N NULL NULL NULL NULL NULL 13 14 370606 NULL NULL NULL 0 NULL NULL NULL NULL NULL N N N N N 2010-01-29 19:05:51.000
432986 370606 X 1 *Change Log DMA NULL NULL N NULL 0 NULL N NULL NULL NULL NULL NULL 16 17 370606 NULL NULL NULL 0 NULL NULL NULL NULL NULL N N N N N 2010-01-29 19:05:51.000
425843 370606 I 0 _Index DMA NULL NULL N NULL 0 Q N NULL NULL NULL NULL NULL 18 21 370606 NULL NULL NULL 0 NULL NULL NULL NULL NULL N N N N N 2010-01-29 19:05:51.000
select * from dbo.project_content
where projid in (select projid from dbo.project where projtree = 370606)
Code:
SID PARENT_SID PROJID ITEMTYPE ITEM_ID ITEM_NAME ITEMDB LEFT_VISIT RIGHT_VISIT TREE_ID VERSION REFERENCE_TYPE REFERENCE_TALIAS INSERT_TS
4693592 NULL 448003 D 8152951 NULL NULL 1 2 4693592 NULL NULL NULL 2009-01-21 16:55:25.000
4367070 NULL 448003 D 8937803 NULL NULL 1 2 4367070 NULL NULL NULL 2008-10-03 14:36:06.000
4367069 NULL 448003 D 8937804 NULL NULL 1 2 4367069 NULL NULL NULL 2008-10-03 14:36:06.000
3835998 NULL 381060 D 8207328 NULL NULL 1 2 3835998 NULL NULL NULL 2008-03-24 13:23:37.000
4446356 NULL 381060 D 9026460 NULL NULL 1 2 4446356 NULL NULL NULL 2008-10-31 12:39:14.000
4863678 NULL 381060 D 9384431 NULL NULL 1 2 4863678 NULL NULL NULL 2009-03-13 19:15:16.000
3998436 NULL 420140 D 8587893 NULL NULL 1 2 3998436 NULL NULL NULL 2008-05-22 20:26:21.000
3998442 NULL 420140 D 8587910 NULL NULL 1 2 3998442 NULL NULL NULL 2008-05-22 20:34:08.000
4366230 NULL 420140 D 8947726 NULL NULL 1 2 4366230 NULL NULL NULL 2008-10-02 19:21:58.000
4013232 NULL 421155 D 8542876 NULL NULL 1 2 4013232 NULL NULL NULL 2008-05-28 18:35:19.000
select item from dbo.document
where docnum in (select item_id from dbo.project_content where projid in (select projid from dbo.projects where projtree = 370606))
Code:
DOCNAME DOCNUM EDITDATE CREATEDATE AUTHOR
Template 8152951 15/11/2007 15/11/2007 DMS
Agr No. 1 8937803 29/09/2008 29/09/2008 LMC
Agr No. 2 8937804 29/09/2008 29/09/2008 LMC
Form Document 8207328 05/12/2007 05/12/2007 DMS
RefCard 9026460 31/10/2008 31/10/2008 MCR
2007 Index 9384431 13/03/2009 13/03/2009 EEL
Removed Documents 8587893 22/05/2008 22/05/2008 TNO
Removed Documents (2008)8587910 22/05/2008 22/05/2008 TNO
SFDS Documents 8947726 02/10/2008 02/10/2008 EEL
Dec. 11/07 version 8542876 07/05/2008 06/05/2008 JCT
Thanks again for your continued assistance with this problem.
|
|
03-15-10, 15:53
|
|
:-)
|
|
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 4,449
|
|
This should return the hierarchy:
Code:
with h (PROJID, projtree, level) as (
select projid, projtree, 1 from dbo.project
where projtree = 370606 and projpid is null
union all
select c.projid, c.projtree, h.level + 1 from dbo.project c, h
where c.projpid = h.projid
and level < 100
)
select
h.level,
p.projname,
d.docname,
...
from h, dbo.project p, dbo.project_content pc, dbo.document d
where h.projid = pc.projid and pc.item_id = d.docnum
and p.projid = h.projtree
Mind you, this is air-code since you did not provide table DDL for me to test it. From here you can obtain desired output by self-joining the results above (if the hierarchy has a constant number of levels) or using PIVOT (check the manual).
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Newbie question on Recursive CTE
