How to list directory path in live report?

DeloitteAdmin_(Delete)_2707331

Hello,I have created a report using the stored procuedre below which lists the documents within a folder and its sub fodlers...i would also like a column that lists the path of each document within the directory structure..is this possible?CREATE PROCEDURE dbo.sp_GetAllChildren_modify (@DataID int,@StartDate DATETIME,@EndDate DATETIME) AS Declare @Count int Declare @SQL varChar(8000) set nocount on set @Count = 1 CREATE TABLE #sp_GetAllChildren_Tmp ( ID int NOT NULL IDENTITY (1, 1), FolderLevel int NOT NULL, Name varChar(255), Version int, VerCDate DATETIME NULL ) ON [PRIMARY] While @Count < 50 Begin Set @SQL = 'insert into #sp_GetAllChildren_Tmp select ' + cast(@Count as varchar(50)) + ', Name, DataID, ParentID, childCount, subtype, IsNull(datasize,0), IsNull(version, 0),vercdate from dtree A with (nolock) left outer join dversdata B with (nolock) on A.dataID = B.DocID WHERE' if @Count = 1 Set @SQL = @SQL + ' dataID = ' + Cast(@DataID as varchar(50)) else Set @SQL = @SQL + ' ParentID in (select dataID from #sp_GetAllChildren_Tmp where FolderLevel = ' + cast(@Count-1 as varchar(50)) + ')' exec (@RowCount = 0 BREAK ELSE Set @Count = @Count + 1 end select count(*) from #sp_GetAllChildren_Tmp AGO

volvostephen

Message from via eLinkThis works but is horribly inefficient....Just take out the parts you need...--drop table [#tmp]Declare @DataID int Declare @Count int Declare @SQL varCHar(8000)set nocount on set @DataID = 5178 set @Count = 1 CREATE TABLE #Tmp ( ID int NOT NULL IDENTITY (1, 1), FolderLevel int NOT NULL, Name varChar(255), SubType int, ChildCount int, DataID int NOT NULL, ParentID int NOT NULL, FullPath varChar(8000) ) ON [PRIMARY] While @Count < 50 Begin Set @SQL = 'insert into #tmp select ' + cast(@Count asvarchar(50)) + ', Name, SubType, ChildCount, DataID, ParentID, NULL fromdtree ' if @Count = 1 Set @SQL = @SQL + 'where dataID = ' + Cast(@DataIDas varchar(50)) else Set @SQL = @SQL + 'where ABS(ParentID) in (selectdataID from #tmp where FolderLevel = ' + cast(@Count-1 as varchar(50)) + ')' print @SQL exec (@SQL) if @@RowCount = 0 BREAK ELSE Set @Count = @Count + 1 end declare @c_curs cursor Declare @DataID INT Declare @tmpPath varChar(8000) Declare @FolderLevel INT Declare @ParentID INT set @c_curs = cursor local FORWARD_ONLY STATIC for select dataid, Folderlevel, name, ParentID from #tmp where subtype <> 0 --and childcount >= 50 open @c_curs fetch next from @c_curs into @DataID, @FolderLevel, @tmpPath,@ParentID while (@@fetch_status = 0) begin While @FolderLevel > 1 Begin Select @FolderLevel = FolderLevel, @tmpPath = Name +':' + @tmpPath, @ParentID = ParentID From #tmp where DataID = @ParentID End Update #tmp Set FullPath = @tmpPath where DataID = @DataID fetch next from @c_curs into @DataID, @FolderLevel,@tmpPath, @ParentID end close @c_curs deallocate @c_cursselect Name, ChildCount, FullPath from #tmp where subtype = 0--and childcount > 50 order by FullPath-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: February 19, 2004 12:32 AMTo: eLink RecipientSubject: How to list directory path in live report?How to list directory path in live report?Posted by DeloitteAdmin on 02/19/2004 12:25 AMHello,I have created a report using the stored procuedre below which lists thedocuments within a folder and its sub fodlers...i would also like a column that lists the path of each document within thedirectory structure..is this possible?CREATE PROCEDURE dbo.sp_GetAllChildren_modify (@DataID int,@StartDateDATETIME,@EndDate DATETIME) AS Declare @Count int Declare @SQL varChar(8000) set nocount on set @Count = 1 CREATE TABLE #sp_GetAllChildren_Tmp ( ID int NOT NULL IDENTITY (1, 1), FolderLevel int NOT NULL, Name varChar(255), Version int, VerCDate DATETIME NULL ) ON [PRIMARY] While @Count < 50 Begin Set @SQL = 'insert into #sp_GetAllChildren_Tmp select ' +cast(@Count as varchar(50)) + ', Name, DataID, ParentID, childCount, subtype, IsNull(datasize,0), IsNull(version, 0),vercdate from dtree A with (nolock)left outer join dversdata B with (nolock) on A.dataID = B.DocID WHERE' if @Count = 1 Set @SQL = @SQL + ' dataID = ' + Cast(@DataID as varchar(50)) else Set @SQL = @SQL + ' ParentID in (select dataID from #sp_GetAllChildren_Tmp where FolderLevel = ' + cast(@Count-1 asvarchar(50)) + ')' exec (@SQL) if @@RowCount = 0 BREAK ELSE Set @Count = @Count + 1 end select count(*) from #sp_GetAllChildren_Tmp AGO[To reply to this thread, use your normal E-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe