Select * from PS_SET_CNTRL_REC a ,(
sELECT PNLGRPNAME,
RECNAME ,rCount,RANK1,PORTAL_URltext, DESCR254 FROM (
SELECT PNLGRPNAME,
RECNAME ,rCount,
RANK() OVER (partition BY RECNAME ORDER BY RECNAME,rCount DESC)AS RANK1 FROM
(SELECT A.PNLGRPNAME,
B.RECNAME ,
COUNT(1) AS rCount
FROM PSPNLGROUP A,
PSPNLFIELD B
WHERE ( A.PNLNAME = B.PNLNAME )
GROUP BY A.PNLGRPNAME,
B.RECNAME
) ) R, PSPRSMDEFN D
WHERE R.PNLGRPNAME= D.PORTAL_URI_SEG2
AND RANK1 <3 AND PORTAL_NAME='EMPLOYEE' AND PORTAL_URltext NOT LIKE '%?%' ORDER BY RANK1) d
,(
select RECNAME,max(ltrim(sys_connect_by_path(FIELDNAME,','),',')) key_field_string
from (select RECNAME,FIELDNAME,
row_number() over(partition by RECNAME order by KEYPOSN) rn
from PSKEYDEFN where indexid='_')
start with rn = 1
connect by prior rn = rn -1
and prior RECNAME = RECNAME
group by RECNAME) k ,(SELECT SYS_CONNECT_BY_PATH (a.portal_label, '->') as Nav, a.portal_uri_seg2 as pnlgrpname from
PSPRSMDEFN a
where a.portal_label NOT LIKE '%Navigation Collections%'
and a.PORTAL_NAME='EMPLOYEE'
START WITH a.portal_prntobjname = 'PORTAL_ROOT_OBJECT'
CONNECT BY nocycle PRIOR a.portal_objname = a.portal_prntobjname) nv
where a.recname=d.recname and setcntrlvalue='00100'
and a.recname=K.recname
and d.pnlgrpname =nv.pnlgrpname
;