RM LiveReport

[brettsanders]

I am trying to write a report that shows all new folders based on date and root folder (specified by user) that do not have rm classifications applied. I have a report that shows all new folders, however i believe my logic is flawed to get the folders that dont have RM applied. Have a look at my sql statement and let me know where i'm wrong.


----start sql----

SELECT ROOT_Folder_Name=(select [Name] FROM [Livelink].[dbo].[DTree] where [DataID] = %1) , CONVERT( varchar , %2, 101 ) as From_Date , Name as New_Folder_Name , dbo.GET_FOLDER_TREE(DataID) as New_Folder_Path , Owner=(SELECT Name FROM KUAF WHERE (ID = DTree.UserID)), LLClassify.CID as Classification, convert(varchar, CreateDate, 102 ) as TheCreateDate , convert(varchar, ModifyDate, 102 ) as TheModifyDate, DTree.* FROM DTree LEFT OUTER JOIN LLClassify ON DTree.DataID = LLClassify.CID WHERE DataID in (select DataID from DTreeAncestors where AncestorID = %1 or DataID = %1) AND (CreateDate > %2 and ((SubType=0) or (SubType=136) or (SubType=201) or (SubType=136) ) and (LLClassify.CID IS NULL) ) order by New_Folder_Path

-----end sql-----

 

[ggriffit]

There is a similar thread at

https://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=12727538&objAction=view

which covers some of this for you.

Also your WHERE clause seems to have two 136's in it :

((SubType=0) or (SubType=136) or (SubType=201) or (SubType=136) )

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

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