tag:blogger.com,1999:blog-34020393.post31093312342225300..comments2024-03-27T10:59:38.307-07:00Comments on Jim's PeopleSoft Journal: Query for Component and/or CREF Navigation Take IIJim Marionhttp://www.blogger.com/profile/12995110203807924786noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-34020393.post-8459606972205962572020-04-19T15:31:00.916-07:002020-04-19T15:31:00.916-07:00This SQL is excellent, but do you any idea how to ...This SQL is excellent, but do you any idea how to add an additional field that would end up with a value showing if the content reference is under any folder that is "hidden" in the portal.<br /><br />I assume the SQL would need to check each folder it's under in the tree. It seems like it might be possible, but changing your SQL is a little advanced for me.<br /><br />Thanks!<br />CaseyCaseyhttps://www.blogger.com/profile/14987243143099103973noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-28113647212313129332014-04-24T09:27:30.710-07:002014-04-24T09:27:30.710-07:00Getting the following error in Oracle 11.2.0.3 ORA...Getting the following error in Oracle 11.2.0.3 ORA-01406 - fetched column value was truncated<br /><br />To avoid this error use TO_CLOB function for both union.<br /><br />TO_CLOB(P.PORTAL_LABEL) AS PATH<br />and<br />TO_CLOB(PATH || ' --> ' || P_ONE.PORTAL_LABEL) AS PATH<br /><br /><br />Ketan Kotharihttps://www.blogger.com/profile/17459077644208180243noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-85674734445179537462013-08-20T11:44:59.016-07:002013-08-20T11:44:59.016-07:00I was actually able to get it to work by casting a...I was actually able to get it to work by casting all the pieces of the contatenation field with to_clob(). Charliehttps://www.blogger.com/profile/04786276214619069696noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-51419280937668318272013-08-14T17:33:48.916-07:002013-08-14T17:33:48.916-07:00@Charlie, interesting. My only other suggestion is...@Charlie, interesting. My only other suggestion is to try the older version <a href="http://jjmpsj.blogspot.com/2007/12/query-for-component-andor-cref.html" rel="nofollow">here</a>Jim Marionhttps://www.blogger.com/profile/12995110203807924786noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-9755817983519181352013-08-14T17:17:10.447-07:002013-08-14T17:17:10.447-07:00I get the following error when trying to run the f...I get the following error when trying to run the first version (list whole registry):<br /><br />ORA-01489: result of string concatenation is too long<br /><br />Any suggestions?Charliehttps://www.blogger.com/profile/04786276214619069696noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-9931154203721156752012-10-27T06:29:27.617-07:002012-10-27T06:29:27.617-07:00Jim,
Sorry for my previous comment, it has no rel...Jim,<br /><br />Sorry for my previous comment, it has no relation to essence of your post, being recursive common table expressions, but was related to finding the menu path in PeopleSoft using sql.<br /><br />I will definitely give the recursive common table expressions a try. <br /><br />I am a big fan of the CONNECT BY PRIOR, since I learned it. I just wanted to share that I have used CONNECT BY PRIOR in a conversion script for a client that was implementing PeopleSoft, to create the department tree, as alternative to using the vanilla TreeMover which consists of an input file with 7 different file layouts. By using the CONNECT BY PRIOR and some PeopleCode I was able to get an hierarchical view and create a department tree very simply and quickly. See for an example with the sql and peoplecode on how I did this:<br />http://bloggingaboutoracleapplications.org/importing-creatint/<br /><br />Keep posting these great mind exhilarating blogs!<br /><br />Regards,<br /><br />Hakan BirogluAnonymoushttps://www.blogger.com/profile/12590396464507309413noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-33655048116639944812012-10-26T03:53:36.804-07:002012-10-26T03:53:36.804-07:00Hi Jim,
Always good to have these SQLs.
A several...Hi Jim,<br /><br />Always good to have these SQLs.<br />A several of years ago, I got the same request from a client that kept forgetting the navigation path to his pages. I had created the following PL/SQL script to find the component in the portal menu.<br />http://bloggingaboutoracleapplications.org/peoplesoft-quick-tip-find-navigationpath-of-a-component/<br /><br />Also, now a days, this functionality is out of the box in PeopleSoft, to be found via Set Up HRMS –> System Administration –> Utilities –> Portal Navigation Path for the users that do not have access directly to the database. See following blog with describes to functionality.<br />http://bloggingaboutoracleapplications.org/path-to/<br /><br />Regards,<br /><br />Hakan BirogluAnonymoushttps://www.blogger.com/profile/12590396464507309413noreply@blogger.com