OraFAQ Forum: SQL & PL/SQL » Convert number to CHAR

Home » SQL & PL/SQL » SQL & PL/SQL » Convert number to CHAR (ORACLE DB SQL)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Convert number to CHAR [message #608109]

Sun, 16 February 2014 04:41

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;

Report message to a moderator

Re: Convert number to CHAR [message #608110 is a reply to message #608109]

Sun, 16 February 2014 04:46

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

to_char(commission_pct)

Report message to a moderator

Re: Convert number to CHAR [message #608111 is a reply to message #608110]

Sun, 16 February 2014 04:51

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;

Report message to a moderator

Re: Convert number to CHAR [message #608112 is a reply to message #608111]

Sun, 16 February 2014 04:55

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

To repeat,
to_char(commission_pct)

Report message to a moderator

Re: Convert number to CHAR [message #608113 is a reply to message #608112]

Sun, 16 February 2014 05:04

hans_cellc
Messages: 11
Registered: February 2014

Junior Member

Sorry to sound stupid but is that not what I have tried in my code?

Report message to a moderator

Re: Convert number to CHAR [message #608115 is a reply to message #608113]

Sun, 16 February 2014 08:28

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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)

Report message to a moderator

Re: Convert number to CHAR [message #608117 is a reply to message #608115]

Sun, 16 February 2014 08:42

Littlefoot
Messages: 21808
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.

Report message to a moderator

Re: Convert number to CHAR [message #608118 is a reply to message #608117]

Sun, 16 February 2014 08:48

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;

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 01:40:46 CDT 2024