Outer joins is just one of the items that is required from time to time, but since I typically never recommend using them with PeopleSoft, I always forget the syntax.  Since my days go back to Oracle 7, I remember the messy (+) notation and so I always end up looking it up.  The good news is Oracle superseded the (+) notation by going to ISO 99 standard as of Oracle 9i, however it still supports (+) notations.

There are three types of joins you can do, left outer, right outer and full outer join. In a left outer join, all the records of the table listed on the left will be returned with the combined results of the join to the table listed on the right, however, if the right listed table does not have a matching row, NULLS will be returned for the columns of the right listed table.  The right outer join does the same thing just in reverse, whereas the full outer join basically does a UNION of the left & right outer joins.

Some database platforms do not support full outer joins so you can basically do it by doing a left outer join combined with with a right outer join using the union statement.

SELECT A.NAME, D.DEPARTMENT FROM EMPLOYEE A LEFT OUTER JOIN DEPARTMENT D ON A.DEPTID = D.DEPTID;

If you want to do the Oracle (+) syntax:

SELECT A.NAME, D.DEPARTMENT FROM EMPLOYEE A, DEPARTMENT D WHERE A.DEPTID = E.DEPTID (+);

When you are at that client site that insists that you must use your own account to interact with PeopleSoft but they don’t set your schema, just change your default schema using the command:

alter session set current_schema=sysadm;

Where sysadm is the default PeopleSoft schema in Oracle, however, PeopleSoft does not have any issues with the schema owner being different then sysadm.  A couple of ways to find the schema, is to look at the table:  psdbowner (ownerid) or psstatus (ownerid). By default the psdbowner table is stored in the PS schema.