Oracle

Question

Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Nov 2006

Posts: 22

Reputation: Lost in Code... is an unknown quantity at this point

Solved Threads: 0

Lost in Code... Lost in Code... is offline

Offline

Newbie Poster

Question

Feb 21st, 2007

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

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?

•

Join Date: Jun 2006

Posts: 7,619

Reputation: ~s.o.s~ has much to be proud of

Solved Threads: 468

~s.o.s~

Offline

Failure as a human

Re: Question

Feb 22nd, 2007

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:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT INITCAP(lastname), INITCAP(firstname), NVL(TO_CHAR(referred), 'test') 
FROM your_table_name ;
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.

Last edited by ~s.o.s~; Feb 22nd, 2007 at 1:52 pm.

I don't accept change; I don't deserve to live.

•

Join Date: Feb 2007

Posts: 1

Reputation: sohel_rana is an unknown quantity at this point

Solved Threads: 0

sohel_rana sohel_rana is offline

Offline

Newbie Poster

Re: Question

Feb 24th, 2007

•

Originally Posted by 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

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

•

Join Date: Jun 2006

Posts: 7,619

Reputation: ~s.o.s~ has much to be proud of

Solved Threads: 468

~s.o.s~

Offline

Failure as a human

Re: Question

Feb 24th, 2007

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.

I don't accept change; I don't deserve to live.