Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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#;
share|improve this answer
    
Yes that is exactly what I was looking for! thank you –  jreed72 Feb 20 '12 at 4:43
    
Or COALESCE() which is standard SQL (and thus available in most DBMS). –  ypercube Feb 20 '12 at 6:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.