Discussions
Categories
Groups
Community Home
Categories
INTERNAL ENABLEMENT
POPULAR
PUBLIC CLOUD
PRIVATE CLOUD
Quick Links
MY LINKS
HELPFUL TIPS
Back to website
Home
Content Management (Extended ECM)
API, SDK, REST and Web Services
Finding a specific character with a LiveReport
Jenny_Benn_(sbsuser03_-_(deleted))
Hi,I need to run a SQL statement whereby it checks a field in the database to see if there are any records whereby the third character is a "/" and then returns all the relevant results.Please can you advise me on this.Thanx
Find more posts tagged with
Comments
Bhupinder_Singh
Message from Bhupinder Singh via eLinkIf your are using an Oracle database, the following will work:select * from dtree where substr(name,3,1) like '/'The above SQL returns all items in DTREE that have an object namecontaining a forward slash (/) in the 3rd character position.If you are using Microsoft SQL server as the database, replace 'substr'with 'substring'.Hope that helps...- Bhupinder------------------------------------------------------------------------- Bhupinder Singh, B.Math., B.Ed. Senior Product Specialist, Customer Support Open Text Corporation, Waterloo, Ontario, Canada Customer support e-mail: support@opentext.com Customer Support Telephone: 800-540-7292 ------------------------------------------------------------------------- -----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Tuesday, September 28, 2004 6:36 AMTo: eLink RecipientSubject: Finding a specific character with a LiveReportFinding a specific character with a LiveReportPosted by Benn, Jenny on 09/28/2004 06:30 AMHi,I need to run a SQL statement whereby it checks a field in the database to see if there are any records whereby the third characteris a "/" and then returns all the relevant results.Please can you advise me on this.Thanx[To reply to this thread, use your normal E-mail reply function.]============================================================Discussion: Livelink LiveReports Discussion
https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink
Server:
https://knowledge.opentext.com/knowledge/livelink.exe
Jenny_Benn_(sbsuser03_-_(deleted))
Hi, thanx so much!! that really helped.I ran into another bit of a problem and am hoping u can help me with this as well...I am using an Oracle database. The sql statement i'm busy with pertains to a file plan with nested folders eg folder 01 contains folders 01/01; 01/02,etc inside of it and this inturn also contains folders eg. 01/01 has folders 01/01/01, etc.i need a report that will display all the folders and nested folders within the file plan + it has to display the RSI's for these folders if there are. If there is no RSI for a certain folder it should just return a blank. Also i previously asked u for help to look for the 3rd character being '/' but the first tier folders doesn't have '/' in them as they are 01, 02. I tried displaying those results by referring to the parentid of the first tier folders but i find that this is not ideal. Since i'm working with physical and electronic folders, i also to distinguish between these as well and hence this report must only display electronic folders not physical folders.I have tried but find that i can either only display all results without their RSI codes or display only those folders that have an RSI.Any help or input would be greatly appreciated.Thanx (:
Bhupinder_Singh
Message from Bhupinder Singh via eLinkIf a folder-hierarchy type of report is desired, then a LiveReport maynot even be needed provided the following solution below issatisfactory:There is a way to get a hierarchical listing of all folders in theLivelink system, starting at the Enterprise Level, or any sub-levelbelow it. The technique is to use the URL of the "Project Outline" page(available by going into a Livelink project, clicking the Project menuand selecting "Outline").Once you have the project outline showing the object types you wish todisplay (filtered on documents, discussions, etc.) replace the projectobject ID (objID) with any folder or workspace ID (even that of a folderexisting outside the project) and you will receive the same outline viewbut for the folder.For example, if the folder for which a report is required has an ID of9205, I could use the following URL to get the report showingsub-folders and documents (replacing "hostname" with the Livelink serverhostname, and replacing "/livelink/" with the appropriate Livelinkservice name, such as "/live91sp3/")
http://hostname/livelink/livelink.exe?func=Projects.ProjectOutline&objid=9205&ItemType_144=&ItemType_0=Notice
that in the above URL, the folder ID (9205) is used as the objID(object ID) value.To get a listing for the Enterprise workspace, first browse to theEnterprise workspace, click on the function icon for the Enterpriseworkspace itself, select Info, General, and look at the URL in thebrowser address box. It may begin with something like:
http://hostname/livelink/livelink.exe?func=ll&objId=2000&objAction=propertiesThe
"objID" reference in the above URL indicates, in this example, thatthe Enteprise Workspace has an object ID of 2000. To get a hierarchicallist of all folders starting at the Enterprise level, we could use a URLsimilar to:
http://hostname/livelink/livelink.exe?func=Projects.ProjectOutline&objid=2000&ItemType_0=Note
: you may get more than just folders in the resulting list; however,that can be considered an idiosyncracy with features out-of-context,i.e. in this case using a project outline feature outside of a project:-(- Bhupinder------------------------------------------------------------------------- Bhupinder Singh, B.Math., B.Ed. Senior Product Specialist, Customer Support Open Text Corporation, Waterloo, Ontario, Canada Customer support e-mail: support@opentext.com Customer Support Telephone: 800-540-7292 ------------------------------------------------------------------------- -----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Wednesday, September 29, 2004 9:43 AMTo: eLink RecipientSubject: RE Finding a specific character with a LiveReportRE Finding a specific character with a LiveReportPosted by Benn, Jenny on 09/29/2004 09:37 AMHi, thanx so much!! that really helped.I ran into another bit of a problem and am hoping u can help me withthis as well...I am using an Oracle database. The sql statement i'm busy with pertainsto a file plan with nested folders eg folder 01 contains folders 01/01;01/02,etc inside of it and this inturn also contains folders eg. 01/01has folders 01/01/01, etc.i need a report that will display all the folders and nested folderswithin the file plan + it has to display the RSI's for these folders ifthere are. If there is no RSI for a certain folder it should justreturn a blank. Also i previously asked u for help to look for the 3rdcharacter being '/' but the first tier folders doesn't have '/' in themas they are 01, 02. I tried displaying those results by referring tothe parentid of the first tier folders but i find that this is notideal. Since i'm working with physical and electronic folders, i also todistinguish between these as well and hence this report must onlydisplay electronic folders not physical folders.I have tried but find that i can either only display all results withouttheir RSI codes or display only those folders that have an RSI.Any help or input would be greatly appreciated.Thanx (:[To reply to this thread, use your normal E-mail reply function.]============================================================Topic: Finding a specific character with a LiveReport
https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=3697209&objAction=viewDiscussion
: Livelink LiveReports Discussion
https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink
Server:
https://knowledge.opentext.com/knowledge/livelink.exe
Jenny_Benn_(sbsuser03_-_(deleted))
Hi thereThanks so much for your input :)Sorry to bother you again but doing it the project outline way looks really good but it still doesn't display the RSI codes and i need that to be displayed on the same page/report as the folders (Folder names consist of a number, followed by a '-' and then a description.) I dont need to show the hierarchy as long as it just lists in order all the folders within the File Plan Repository folder (this is the parent folder that contains all the folders and nested folders). The results displayed by the LiveReport should only be the electronic folders (not physical) and should look as such: File Name Disposal Schedule (RSI)01 - Legislation01/01 - Legislation and Judicature01/02 - Routine Enquiries 01/01/01 - Tabling 01/01/01/01 - Projects A2002 - Organisation and Control02/01 - Functions and Services D502/01/P - Policy D503 - Finances D1004 - Personnel Matters04/01 - Routine Enquries04/01/01 - Functions and Services A20As i previously mentioned, not all the files have disposal schedules, those that dont should display only the name as above. Just to re-mention the other problem that i'm having: as u can see in the example above some of the file names dont have a '/' because they only consist of '01', for these i had to refer to the objectid of the parent folder (File plan repository folder). I hope I managed to explain this clearly to you...if you have any additional questions then dont hesitate to ask me. Thanks for your time.The ff statement returns all those folders that have a classification, i need it to return those folders that dont have a classification as well."select Dtree.*, Rimsnodeclassification.rimsrsi from Dtree, Rimsnodeclassification where substr(name,3,1) like '/' and dtree.dataid = rimsnodeclassification.nodeid order by dtree.name"And this statement displays all the folders within the parent folder but without the RSI's :"select name from Dtree where substr(name,3,1) like '/' and subtype = 0 or parentid = 18944 order by name"
Jenny_Benn_(sbsuser03_-_(deleted))
The example in the previous reply didn't display properly when i posted the reply. It should be two columns basically: one with the file/folder name (i.e file number - description) and another column with the disposal schedules/rsi's (i.e A20, D5, A10 if applicable)
Jenny_Benn_(sbsuser03_-_(deleted))
Hi thereOk i managed to get a shorter SQL statement that displays the parent folder and all the nested folders in order. The statement is as follows:select * from dtree where subtype in (0) start with dataid = 18944 connect by prior dataid = parentid order by nameNow all i need is just to add to this statement - so that the Livereport displays another column for the corresponding RSI codes for these folders in the file plan and for the folders that dont have an RSI, the field should just be blank.Really hope you can help. Thanx so much for everything thus far
Bhupinder_Singh
Message from Bhupinder Singh via eLinkWouldn't you just include in the query the table in which the RSI codeis stored, and reference the corresponding column? For example, if thecode is in a table called "FolderCodes" and is stored in a column called"CodeID", then assuming that the dataID of the folder is also stored inthe "FolderCodes" table as a foreign key to dataID in DTree, your SQLwould look something like this:select a.*, b.CodeID from dtree a, FolderCodes b where a.subtype in (0)and a.dataID=b.dataID start with a.dataid = 18944 connect by priora.dataid = a.parentid order by a.name- Bhupinder------------------------------------------------------------------------- Bhupinder Singh, B.Math., B.Ed. Senior Product Specialist, Customer Support Open Text Corporation, Waterloo, Ontario, Canada Customer support e-mail: support@opentext.com Customer Support Telephone: 800-540-7292 ------------------------------------------------------------------------- -----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Monday, October 11, 2004 3:23 AMTo: eLink RecipientSubject: RE RE Finding a specific character with a LiveReportRE RE Finding a specific character with a LiveReportPosted by Benn, Jenny on 10/11/2004 03:20 AMHi thereOk i managed to get a shorter SQL statement that displays the parentfolder and all the nested folders in order. The statement is as follows:select * from dtree where subtype in (0) start with dataid = 18944connect by prior dataid = parentid order by nameNow all i need is just to add to this statement - so that the Livereportdisplays another column for the corresponding RSI codes for thesefolders in the file plan and for the folders that dont have an RSI, thefield should just be blank.Really hope you can help. Thanx so much for everything thus far
[To reply to this thread, use your normal E-mail reply function.]============================================================Topic: Finding a specific character with a LiveReport
https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=3697209&objAction=viewDiscussion
: Livelink LiveReports Discussion
https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink
Server:
https://knowledge.opentext.com/knowledge/livelink.exe
Jenny_Benn_(sbsuser03_-_(deleted))
Hello :)I tried that and when i run the report, it says "No results found." The RSI codes for the file plan folders that have RSI's are stored in the Rimsnodeclassification table. The only reference to the dtree table is that the nodeid in the rimsnodeclassification table is equal to the dataID in the dtree table for those folders that have an RSI code. The Rimsnodeclassification table only stores those folders with a RSI code so the Parent Folder (whose parentID is 2000 and dataID is 18944 in dtree table) for example isn't referenced in the Rimsnodeclassification table at all since it doesn't have a RSI code.If you do have another option for me to consider please let me know.Thanx
