Here are some good SQL's to have in your library.
When a process name is known:
SELECT DISTINCT
[Link],
[Link],
[Link],
[Link] as Component,
'Home > ' || RTRIM([Link]) || ' > ' || RTRIM([Link]) || ' > ' ||
RTRIM([Link]) || ' > ' || [Link] as Location
FROM PSMENUDEFN M,
PSMENUITEM ITEM,
PS_PRCSDEFNPNL PAGE,
PS_PRCSDEFN PRCS
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = 'PY_PULL_COST'
When a component name is known:
SELECT DISTINCT [Link] , [Link] , [Link] ,
[Link] as Component , 'Home > ' || RTRIM([Link]) || '
> ' ||
RTRIM([Link]) || ' > ' || RTRIM([Link]) || ' > ' ||
[Link] as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PS_PRCSDEFNPNL PAGE ,
PS_PRCSDEFN PRCS
WHERE [Link] = [Link] [Link] =
[Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = 'component-name'
When a record name is known:
SELECT DISTINCT [Link] ,
[Link] as Page , 'Home > ' || RTRIM([Link]) || ' > ' ||
RTRIM([Link]) || ' > ' || RTRIM([Link]) || ' > ' ||
[Link] as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PSPNLGROUP COMP ,
PSPNLFIELD PFLD
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = 'record-name'
When a page name is known:
SELECT DISTINCT [Link] as Page , 'Home > ' ||
RTRIM([Link]) || ' > ' || RTRIM([Link]) || ' > ' ||
RTRIM([Link]) || ' > ' || [Link] as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PSPNLGROUP COMP
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] = 'page-name'
Posted by Jeromy McMahon at 12:50 PM
5 comments:
Jeromy McMahon said...
You might need to add a || for concat.
When a component name is known:
SELECT DISTINCT [Link] , [Link] , [Link] ,
[Link] as Component , 'Home > ' ||
RTRIM([Link]) || ' > ' ||
RTRIM([Link]) || ' > ' || RTRIM([Link]) || ' > ' ||
[Link] as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PS_PRCSDEFNPNL
PAGE , PS_PRCSDEFN PRCS
WHERE [Link] = [Link]
[Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = 'component-name'
When a record name is known:
SELECT DISTINCT
[Link],
[Link] as Page,
'Home > ' || RTRIM([Link]) || ' > ' || RTRIM([Link])
|| ' > ' || RTRIM([Link]) || ' > ' || [Link] as MenuPath
FROM PSMENUDEFN MEN,
PSMENUITEM ITEM,
PSPNLGROUP COMP,
PSPNLFIELD PFLD
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] = [Link]
AND [Link] = 'CONTRACT_DATA'
select rtrim(sysdate) from dual
select * from PSMENUDEFN
When a page name is known:
SELECT DISTINCT [Link] as Page , 'Home > ' ||
RTRIM([Link]) || ' > ' || RTRIM([Link]) || ' > ' ||
RTRIM([Link]) || ' > ' || [Link] as MenuPath
FROM PSMENUDEFN MENU , PSMENUITEM ITEM , PSPNLGROUP
COMP
WHERE [Link] = [Link]
AND [Link] = [Link]
AND [Link] = 'page-name'
January 14, 2008 4:04 PM
ronie said...
Hi jeromy,
can i post this in our tips and tricks for our team? This is very useful..
ronie
March 29, 2008 9:51 AM
Anonymous said...
Good post. If you want to remove all the &'s from the path names, you could
substitute RTRIM(column_name) with REPLACE(RTRIM(column_name),'&','').
Ex.
BEFORE: Home > Manage Student &Records > Track &Student Careers > &Use
> Student &Program/Plan
AFTER: Home > Manage Student Records > Track Student Careers > Use >
Student Program/Plan
Occasionally, that function would remove an & that you would actually want (like
the one in 'Process Interest & Payment'). But, that would be rare.
May 7, 2008 11:37 AM
Jeromy said...
Yes, you can freely use this code.
May 9, 2008 11:13 AM
Anonymous said...
Jeromy, you seem to be very good with this stuff.
I need to replicate the following SQL in the PeoplTools Query tool so my users
can run it. I am having trouble defining it. I know I need to use an expression, but
is is not going well. Can you help. Feel free to call me at 720-913-4931. Thank
you. Bonnie
select [Link], count(*) from PS_SEC_BU_CLS X
where [Link] like ('D8%')
having count(*) > '1'
group by [Link]