Recursive Query to get Document count in a folder

Manoj Agrawal

 

Could anyone in group Validate below query to get count of all documents in a particular folder. Want to get experts in this forum to review this before providing numbers to users.  

WITH cte_name 
AS
(
select dataId, parentid, subtype
from dtree where dataid = '63318' --DISABILITY MASTER FOLDER ID IN QA
UNION ALL
select dt.dataid, dt.parentid, dt.subtype from dtree dt
inner join cte_name as cte 
on  dt.parentid = cte.dataId
)
SELECT count(1)
FROM cte_name 
where cte_name.subtype in ('144')

Thanks, Manoj

David Templeton

Manoq

 

What version are you using?  There is a cleaner, less impactful way of getting the count.  But I would ask, like I always do, why oh why?  This type of query will really, really hurt on large sets.  Realize that count(*) can be blocked until all updates are complete since that function wants to be ‘accurate’.  Do you really need the real count, that is not really real, or some high watermark value ot say “more than N objects exist”?

 

 

David