Question

[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.

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

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

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?

[~s.o.s~]

This happens since the referred column contains numric data and you are trying to substitute it with a string when null is encountered. You just happened to place the TO_CHAR function at the wrong place.

Do something like:

SELECT INITCAP(lastname), INITCAP(firstname), NVL(TO_CHAR(referred), 'test') 
FROM your_table_name ;

Try it out and let me know if it works or not.

[sohel_rana]

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.

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

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

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?

see this you will understand----

SQL> create table test
2 (fname varchar2(15),
3 last_name varchar2(10),
4 refered number(10));

Table created.

SQL> insert into test
2 values('xx','yy',1000);

1 row created.

SQL> insert into test
2 values('xx','yy',null);

1 row created.

SQL> select last_name,fname,nvl(to_char(refered),'Not Refered') from test;

LAST_NAME FNAME NVL(TO_CHAR(REFERED),'NOTREFE
---------- --------------- ----------------------------------------
yy xx 1000
yy xx Not Refered

SQL> select last_name,fname,nvl(to_char(refered),to_char(999999)) from test;

LAST_NAME FNAME NVL(TO_CHAR(REFERED),TO_CHAR(9
---------- --------------- ----------------------------------------
yy xx 1000
yy xx 999999

for further reference see oracle 9i documentation. try to understand the syntax of nvl,nvl2,nullif etc. and also the difference between them

[~s.o.s~]

It would be really good if you posted help in normal font colors and not use colors like red and blue which make the eyes of the reader bleed...And use code tags while posting code.

[Sulley's Boo]

^

OK edit it plz <_<

[Musta]

I prefare the Red and green color...

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