Home » SQL & PL/SQL » SQL & PL/SQL » Convert number to CHAR (ORACLE DB SQL)
Convert number to CHAR [message #608109] Sun, 16 February 2014 04:41 Go to next message
hans_cellc
Messages: 11
Registered: February 2014
Junior Member
The following code gives and error of invalid number, I know why. It is because the commission_pct is expecting a number and nor char but how do I fix it.
I am suspecting to convert it to char but how?

SELECT last_name, NVL(commission_pct, 'NO COMMISSION')
FROM employees;
Re: Convert number to CHAR [message #608110 is a reply to message #608109] Sun, 16 February 2014 04:46 Go to previous messageGo to next message
John Watson
Messages: 4102
Registered: January 2010
Location: Global Village
Senior Member
to_char(commission_pct)
Re: Convert number to CHAR [message #608111 is a reply to message #608110] Sun, 16 February 2014 04:51 Go to previous messageGo to next message
hans_cellc
Messages: 11
Registered: February 2014
Junior Member
Thanks for quick response.
I have tried the following code with no success.

SELECT last_name, TO_CHAR(commission_pct), NVL(commission_pct, 'NO COMMISSION')
FROM employees;


and

SELECT last_name, TO_CHAR(NVL(commission_pct, 'NO COMMISSION'))
FROM employees;
Re: Convert number to CHAR [message #608112 is a reply to message #608111] Sun, 16 February 2014 04:55 Go to previous messageGo to next message
John Watson
Messages: 4102
Registered: January 2010
Location: Global Village
Senior Member
To repeat,
to_char(commission_pct)
Re: Convert number to CHAR [message #608113 is a reply to message #608112] Sun, 16 February 2014 05:04 Go to previous messageGo to next message
hans_cellc
Messages: 11
Registered: February 2014
Junior Member
Sorry to sound stupid but is that not what I have tried in my code?
Re: Convert number to CHAR [message #608115 is a reply to message #608113] Sun, 16 February 2014 08:28 Go to previous messageGo to next message
BlackSwan
Messages: 21982
Registered: January 2009
Senior Member
hans_cellc wrote on Sun, 16 February 2014 03:04
Sorry to sound stupid but is that not what I have tried in my code?

No, that is not what you tried (as posted)
Re: Convert number to CHAR [message #608117 is a reply to message #608115] Sun, 16 February 2014 08:42 Go to previous messageGo to next message
Littlefoot
Messages: 18847
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You have tried it, but on wrong place(s). Try to use it within (i.e. inside) the NVL function.
Re: Convert number to CHAR [message #608118 is a reply to message #608117] Sun, 16 February 2014 08:48 Go to previous message
hans_cellc
Messages: 11
Registered: February 2014
Junior Member
Thanks a lot Littlefoot, and below my code that now works.

SELECT last_name, NVL(TO_CHAR(commission_pct), 'NO COMMISSION')
FROM employees;
Previous Topic: how to search the package object_type from all_objects
Next Topic: What are valid and invalid functions in PL-SQL
Goto Forum:
  


Current Time: Thu Apr 24 21:49:10 CDT 2014

Total time taken to generate the page: 0.13808 seconds