Walking through folder structure using a livereport 1

[koenbonnet]

Hi all,

Fairly often i get the question to report about all documents in a certain folder, including its subfolders. I'd like to use a livereport for it, but using SQL server there is no standard way to query the hierarchy in the dtree table from a certain parentid.

Do you have this same issue? I'm now working on a SQL Server function that allows to loop the hierarchy in a SQL Query.

Regards,

Koen

 

[appnair]

in oracle there is the famous tree walk using the connect by.In SQL server you have to write a function for the same.LR's used to have a difficulty of executing stored procs'.I do not know whether version 9.2 and 9.5 supports them.If you search the knowledge base there are examples of sql server tree walks.

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

 

[VOLFONI]

try somthing like this
select lpad (' ',5 * level,'-') || name "Full name",dataid from dtree start with dataid=%1 connect by parentid= prior dataid


regards

 

[koenbonnet]

try somthing like this
select lpad (' ',5 * level,'-') || name "Full name",dataid from dtree start with dataid=%1 connect by parentid= prior dataid

It's good mentioning this, but this statement is Oracle specific and will not work on SQL Server. SQL Server does support a feature named User Defined Functions. I think those can be used to solve this puzzle.

 

[koenbonnet]

I now have written a function (UDF - User Defined Function) on SQL Server to traverse through a hierarchical table such as dtree. This problem is solved for me. If anyone has the same problem now or in the future, i may be of help.

Thanks.

 

[VOLFONI]

Hey,
why don't you share with others what you've done instead of saying : I found it

 

[ggriffit]

I'd be interested to host a copy of the script at

http://www.greggriffiths.org/livelink/

 

[olya]

Here is the easy T-SQL code that I use to get hierarchical data from Dtree.

CREATE PROCEDURE DBO.sp_GetLLChildren
(@ParentID int = 2000, @Subtype int = Null)

AS

DECLARE @Level int
SET @Level = 0

SELECT DataID as DataID, @Level as ChildLevel INTO #T FROM dbo.Dtree WHERE ParentID = @ParentID

WHILE @@RowCount > 0
BEGIN
IF (@Level >= 100) BREAK --if we are going 100 levels down, something must have gone wrong
SET @Level = @Level + 1
INSERT INTO #T
SELECT DataID, @Level FROM
dbo.Dtree WHERE ParentID IN
(SELECT DataID FROM #T WHERE ChildLevel = @Level -1)
END

IF @Subtype IS NULL --get all objects
SELECT DataID FROM #T ORDER BY ChildLevel
ELSE --get only objects of particular subtype
SELECT #T.DataID FROM #T INNER JOIN dbo.Dtree ON #T.DataID= dbo.Dtree.DataID WHERE dbo.Dtree.subtype=@Subtype ORDER BY ChildLevel

DROP TABLE #T

GO

Enjoy!
Olga

 

[menu]

Greeting all:
I have created a category that has three fields one of them is "Date" field and I want the user every time he adds this category to have a default value of current date + 7 days. Can anyone help?

Thanks,

Alshreef

 

[koenbonnet]

I have created a category that has three fields one of them is "Date" field and I want the user every time he adds this category to have a default value of current date + 7 days. Can anyone help?

I think this one deserves a separate topic, Alshreef

Koen

 

[menu]

Apologize it was a mistake