0

I have a school problem, I'm not going to try and hide it. This is for Oracle9i: SQL class. The question we have to write a code for is:
Create a list of customers that will display the characters 'NOT REFERRED' if the customer was not referred by another customer. The reffered column shows a number if reffered for example, 1204. I know I need to use TO_CHAR to change it to string and then NVL to put 'NOT REFERRED'. My quesiton is, if I run this, it gives me an error. I know how to do one at a time, but not together.

SELECT INITCAP(lastname), INITCAP(firstname), TO_CHAR(NVL(referred, 0009999))
FROM customers

if i try adding NVL and (), I get 9999 and when I add 'NOT REFERRED' for my TO_CHAR, I get error:
SELECT INITCAP(lastname), INITCAP(firstname), TO_CHAR(NVL(referred, 'test'))
*
ERROR at line 1:
ORA-01722: invalid number

Any suggestions?

2
Contributors
1
Reply
2
Views
10 Years
Discussion Span
Last Post by nikkiH
0

TO_CHAR doesn't fail on null I don't think.

Try:
NVL(TO_CHAR(referred), 'NOT REFERRED')

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.