Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2010
    Posts
    9

    Unanswered: 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.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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 ...
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    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??

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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'
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Mar 2010
    Posts
    9
    Quote Originally Posted by n_i View Post
    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 19:10.

  6. #6
    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

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Please provide a consistent example of your data, along with the desired output (based on that example).
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Mar 2010
    Posts
    9
    Quote Originally Posted by n_i View Post
    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 11:56.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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 View Post
    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 View Post
    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.
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Mar 2010
    Posts
    9
    Quote Originally Posted by n_i View Post
    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.

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by brettdmd View Post

    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)?
    ---
    "It does not work" is not a valid problem statement.

  12. #12
    Join Date
    Mar 2010
    Posts
    9
    Quote Originally Posted by n_i View Post
    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 13:14.

  13. #13
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  14. #14
    Join Date
    Mar 2010
    Posts
    9
    Quote Originally Posted by n_i View Post
    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.

  15. #15
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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).
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •