Folder traversal livereport in MSSQL

[DrDDT]

Hi!

How can I do a recursive folder traversal in a Livereport using MSSQL?
In Oracle, I always use 'connect by prior', but MSSQL has no such command.

 

[appnair]

you need to create a sql function.You get that function when you attend the LR course.

But here's a good link in case you want to see how you can do it yourself

http://blogs.conchango.com/christianwade/archive/2004/11/09/234.aspx

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer and probably certfiable,Livelink ECM Champion 2008

http://www.linkedin.com/in/appunair

 

[livelink10]

connect by prior degrades in performance as the height of the tree increases.

You can also use DTreeAncestors which holds the materialized tree structure of all of the object in LES. Thsi method then allows a simple join to be done.

 

[appnair]

dtreeanacestors is a table coded and maintained by oscript callbacks.Does it actually reflect all the changes if I remember correctly you had to turn on recommender and monitor it working correctly all the time.Maybe they may have improved it but I use this link SQLserver function from KB to do my work(that too 32 is the level of recursion that is allowed)

https://knowledge.opentext.com/knowledge/llisapi.dll/open/16344253

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer and probably certfiable,Livelink ECM Champion 2008

http://www.linkedin.com/in/appunair

 

[livelink10]

Hmm, I would agree to most of your points, it had a shaky start to life There have been a number of fixes made for it in the monthly patches. Of course, in the end the choice is up to the end programmer and thier comfort level etc.

As for being part of Recommneder, that was an unfortunate placement at the time I think. I for one think hindsight is a wonderful thing

 

[DrDDT]

I've never used stored procedures in a Livereport.
How do I use it?
Can I use the results of the procedure to get other information, or do I have to modify the procedure to get the results I want, because it's the only thing I can run from a Livereport?

 

[DrDDT]

I found a working stored procedure, and now I can get output by running it from a Livereport.

I still have the question if I can use it in another query in the livereport, or that I have to do everything I want in the stored procedure.

Can I do something like:

select * from dtree where id in (sp_GetTree)

 

[ggriffit]

Not sure you can do that, see

http://www.4guysfromrolla.com/webtech/sqlguru/q013000-2.shtml.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

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

 

[DrDDT]

I googled that a stored procedure cannot do that.

It is working though using a user function!

I can do a 'select DataID from getLLTree(537310)'
Where getLLTree is a user function that does the treewalking.

 

[ggriffit]

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

has some examples of how do this.

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

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

 

[DrDDT]

Thanks Greg, the user function on your website is very useful.
I found another one that does work slightly different, and also returns the level:

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION getLLTree(@ParentID int = 2000)
RETURNS @Result TABLE
	(
		DataID int NOT NULL,
		SubType int NOT NULL, 
		ChildLevel int NOT NULL 
	)
AS
BEGIN

DECLARE @Level int
SET @Level = 0

INSERT INTO @Result SELECT DataID as DataID, SubType as SubType, @Level as ChildLevel FROM 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 @Result
SELECT DataID, Subtype, @Level FROM 
Dtree WHERE ParentID IN
(SELECT DataID FROM @Result WHERE ChildLevel = @Level -1)
END
    
	RETURN
END

 

[appnair]

Be careful on the hardcode 2000.I have worked in ll implementation where the EWS was not 2000 but 2001.I think I posted this info at the KB.

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937
Certified OT Developer,Livelink ECM Champion 2008

http://www.tek-tips.com/faqs.cfm?fid=2884

http://www.linkedin.com/in/appunair