Find Navigation of Page from Component Name

Your rating: None Average: 4.3 (10 votes)

Many times we came across situation where we have Page name but we dont know the navigation for this page in Portal.

Following SQL makes the job easy for us.
Execute the following SQL in Database.

SELECT REPLACE(navigation,'',' > ') "PIA Navigation"
, url
, MENU_NAME
, COMPONENT_NAME
, portal_objname
, portal_prntobjname
, portal_uri_seg3
,portal_label
FROM (SELECT SYS_CONNECT_BY_PATH (a.portal_label,'>>') navigation
, '/EMPLOYEE/ERP/c/' || a.portal_uri_seg1 || '.' || a.portal_uri_seg2 || '.' || a.portal_uri_seg3 url
,  a.portal_uri_seg1  MENU_NAME
, a.portal_uri_seg2  COMPONENT_NAME
, a.portal_objname   portal_objname
, a.portal_prntobjname  portal_prntobjname
, a.portal_uri_seg3 portal_uri_seg3
, a.portal_reftype portal_reftype
,a.portal_label portal_label
FROM (SELECT DISTINCT a.portal_name
, a.portal_label
, a.portal_objname
, a.portal_prntobjname
, a.portal_uri_seg1
, a.portal_uri_seg2
, a.portal_uri_seg3
, a.portal_reftype
FROM psprsmdefn a
WHERE portal_name = 'EMPLOYEE'
AND portal_objname <> portal_prntobjname
AND NOT EXISTS (SELECT 'X'
FROM psprsmsysattrvl
WHERE portal_name = a.portal_name
AND portal_reftype = a.portal_reftype
AND portal_objname = a.portal_objname
AND portal_attr_nam = 'PORTAL_HIDE_FROM_NAV'
AND a.portal_objname NOT IN ('CO_NAVIGATION_COLLECTIONS','PORTAL_BASE_DATA'))) a
WHERE portal_uri_seg2 LIKE '%COMP_NAME%'
START WITH a.portal_prntobjname = 'PORTAL_ROOT_OBJECT'
CONNECT BY PRIOR a.portal_objname = a.portal_prntobjname)
WHERE navigation NOT LIKE '%Navigation Collections%'

Where as "COMP_NAME" is component name of page which you are looking for.

Tell me if this seems to be useful for You.

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Guest's picture
Guest (not verified)
Re: Find Navigation of Page from Component Name

Works great. This is really helpful. Thanks !!

Guest's picture
Guest (not verified)
Re: Find Navigation of Page from Component Name

Works great. This is really helpful. Thanks !!

Guest's picture
Guest (not verified)
Re: Find Navigation of Page from Component Name

, portal_objname --Name of Content Ref Administration
, portal_prntobjname --Parent Name of content Ref Administration

bosmanjc's picture
User offline. Last seen 8 years 47 weeks ago. Offline
Joined: 07/23/2010
Posts: 14
Re: Find Navigation of Page from Component Name

i'm trying to get it working on db2

Farhan Akhlaq's picture
Farhan Akhlaq (not verified)
Re: Find Navigation of Page from Component Name

I am trying to run this query on SQL Server but its giving following error

" Msg 195, Level 15, State 10, Line 9
'PS_SYS_CONNECT_BY_PATH' is not a recognized built-in function name.
Msg 102, Level 15, State 1, Line 35
Incorrect syntax near 'a'.
Msg 319, Level 15, State 1, Line 37
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. "

Any suggestion?

Guest's picture
Guest (not verified)
Re: Find Navigation of Page from Component Name

Thanks for the SQL. It's really very useful.

Guest's picture
Guest (not verified)
Re: Find Navigation of Page from Component Name

Hi,

It is very helpful.... Thanks so much for sql.

Yashwanth Asady's picture
Yashwanth Asady (not verified)
Re: Find Navigation of Page from Component Name

Replace 'component name' with the component you are trying to navigate to:

SELECT DISTINCT P4.PORTAL_LABEL,P3.PORTAL_LABEL, P2.PORTAL_LABEL L2, P1.PORTAL_LABEL L1, P1.DESCR254
FROM PSPRSMDEFN P1, PSPRSMDEFN P2, PSPRSMDEFN P3, PSPRSMDEFN P4
WHERE P1.PORTAL_URI_SEG2 = 'component name'
AND P1.PORTAL_PRNTOBJNAME = P2.PORTAL_OBJNAME
AND P2.PORTAL_PRNTOBJNAME = P3.PORTAL_OBJNAME
AND P3.PORTAL_PRNTOBJNAME = P4.PORTAL_OBJNAME
AND P1.PORTAL_NAME = P2.PORTAL_NAME
AND P2.PORTAL_NAME = P3.PORTAL_NAME
AND P3.PORTAL_NAME = P4.PORTAL_NAME

ermayank's picture
User offline. Last seen 7 years 21 weeks ago. Offline
Joined: 01/27/2009
Posts: 5
Re: Find Navigation of Page from Component Name

In 9.0 and latter you can have this from page also,Navigation for the same is -

Main Menu--> Enterprise Components--> Find Object Navigation

Tanmaya's picture
Tanmaya (not verified)
Re: Find Navigation of Page from Component Name

Very good solution. Works great...Keep posting like this...:)

Guest's picture
Guest
Re: Find Navigation of Page from Component Name

It is very helpful.... Keep it up...