Ad:

Oracle Discussion Thread
Unsolved
Views: 4709

Feb 21st, 2007

Question

Expand Post »

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?

Similar Threads

C command-line I/O question in C++
Apache Alias Directive... mod_alias question in Linux Servers and Apache
Completely new to C++ and have question about using char in C++
Question in Geeks' Lounge
question on cooling in Cases, Fans and Power Supplies

Lost in Code...

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

22 posts
since Nov 2006

Permalink

Feb 22nd, 2007

Re: Question

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:

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.

~s.o.s~

Super Moderator

Featured Poster

Reputation Points: 3241

Solved Threads: 719

Failure as a human

Offline

8,873 posts
since Jun 2006

Permalink

Feb 24th, 2007

Re: Question

Click to Expand / Collapse Quote 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

sohel_rana

Reputation Points: 13

Solved Threads: 0

Newbie Poster

Offline

1 posts
since Feb 2007

Permalink

Feb 24th, 2007

Re: Question

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.

~s.o.s~

Super Moderator

Featured Poster

Reputation Points: 3241

Solved Threads: 719

Failure as a human

Offline

8,873 posts
since Jun 2006

Permalink

Feb 26th, 2007

Re: Question

^

OK edit it plz <_<

Sulley's Boo

Reputation Points: 529

Solved Threads: 10

Posting Pro in Training

Offline

450 posts
since Dec 2004

Permalink

Aug 23rd, 2007

Re: Question

I prefare the Red and green color...

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

Musta

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

6 posts
since Aug 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Oracle Forum Timeline: Time-based updating

Next Thread in Oracle Forum Timeline: How to create script of a user/role

DaniWeb Message

Cancel Changes

User Name
Password