I've reviewed other posts as well as Oracle documentation on replacing the older style (+) joins with ANSI format joins. I'm having a hard time converting this over to what I assume to be two left outer join operations. My hangup is the two from select statements and where exactly the joins should be placed.
Here is my query:
SELECT
AUP.USERNAME,
AUP.MENU_STRING MODULE,
NVL(UGA.PERMISSION,AUP.DEFAULT_PERMISSION) PERMISSION
FROM
(SELECT
DU.USERNAME,
A.PROGRAM_ID,
A.MENU_STRING,
'Y' DEFAULT_PERMISSION
FROM
APPLICATION A,
DBA_USERS DU
WHERE
A.PROGRAM_ID NOT IN ('.SEPARATOR')
AND DU.USERNAME NOT LIKE '%#') AUP,
(SELECT
USER_ID,
PROGRAM_ID,
PERMISSION
FROM
USER_PGM_AUTHORITY
WHERE
PROGRAM_COMPONENT='PROGRAM') UGA
WHERE
AUP.USERNAME=UGA.USER_ID(+)
AND AUP.PROGRAM_ID=UGA.PROGRAM_ID(+)
AND aup.menu_string = 'Vendor Maintenance'
ORDER BY
AUP.USERNAME,
AUP.MENU_STRING;
See Question&Answers more detail:os