Member Avatar for Lost in Code...

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
  • 297 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by nikkiH
Member Avatar for nikkiH

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

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.