Select p.*,xlat.descr_string from (SELECT FIELDNAME ,
rtrim(MAX(SYS_CONNECT_BY_PATH(descr,'~~')),'~~') as descr_string
FROM
(SELECT FIELDNAME,
FIELDVALUE
||' '
||B.XLATLONGNAME
||' '
||B.XLATSHORTNAME AS DESCR,
row_number() over(partition BY FIELDNAME order by FIELDVALUE
||' '
||B.XLATLONGNAME
||' '
||B.XLATSHORTNAME) AS rn,
COUNT(1) over(partition BY FIELDNAME ) AS rCount
FROM PSXLATITEM B
WHERE B.EFFDT =
(SELECT MAX(B_ED.EFFDT)
FROM PSXLATITEM B_ED
WHERE B.FIELDNAME = B_ED.FIELDNAME
AND B.FIELDVALUE = B_ED.FIELDVALUE
AND B_ED.EFFDT <= SYSDATE
)
)
START WITH rn =1
CONNECT BY prior rn=rn-1
AND rn < 40
AND prior FIELDNAME = FIELDNAME
GROUP BY FIELDNAME
) XLAT, (SELECT A.PNLNAME, A.RECNAME, A.FIELDNAME, A.LBLTEXT, C.PORTAL_LABEL, C.DESCR254, C.PORTAL_URLTEXT,C.PORTAL_NAME,C.PORTAL_OBJNAME
FROM PSPNLFIELD A, PSPNLGROUP B, PSPRSMDEFN C
WHERE ( A.PNLNAME = B.PNLNAME
AND B.PNLGRPNAME = C.PORTAL_URI_SEG2 )) p
where xlat.FIELDNAME(+)=p.FIELDNAME
and p.PORTAL_NAME='EMPLOYEE'