update with nested join problem First of all newbie here and to the world of Oracle, just trying to get some code working:

UPDATE CUSTOMERS
SET RE.LASTNAME = 'NO REFERRAL' 
WHERE RE.LASTNAME IS NULL
   (SELECT CU.CUSTOMER#, INITCAP(CU.LASTNAME || ', ' || SUBSTR(CU.FIRSTNAME,1,1)) ||'.' AS "CUSTOMER NAME",INITCAP(RE.LASTNAME) AS "REFERRED LAST NAME", INITCAP(RE.FIRSTNAME) AS "REFERRED FIRST NAME" 
FROM CUSTOMERS CU LEFT OUTER JOIN CUSTOMERS RE
ON CU.REFERRED = RE.CUSTOMER#);


The nested block works fine by giving me a listing of all customers and the person first and last name who referred them including customers who have no one who has referred them. The problem I'm having is the first and last name of referrer will come up as NULL for the people with no referrer. (as it should with the left join), but instead of saying null I would like it to say "No Referral" in the column instead. 
I also had the referrer column concatenated together like the customer name at one point, but couldn't get it to do as I wanted hence splitting the name back up. If there is a solution to either problem I would be grateful as it has me stumped.
You can use NVL to return a default string when a null value is encountered:

SELECT NVL(fieldname,'Show this text instead of null')
FROM myTable;


In your case, I would try something like this:

SELECT CU.CUSTOMER#, INITCAP(CU.LASTNAME || ', ' || SUBSTR(CU.FIRSTNAME,1,1)) ||'.' AS "CUSTOMER NAME",
   INITCAP(NVL(RE.LASTNAME,'No Referral')) AS "REFERRED LAST NAME", 
   INITCAP(NVL(RE.FIRSTNAME,'No Referral')) AS "REFERRED FIRST NAME" 
FROM CUSTOMERS CU
LEFT OUTER JOIN CUSTOMERS RE ON CU.REFERRED = RE.CUSTOMER#;