| Character to number (merged) [message #263785] |
Fri, 31 August 2007 00:53  |
tondapi
Messages: 99
Registered: August 2007
Location: usa
|
Member |
|
|
Hi
I have another problem,
i have 2 number column loc,abc.i converted number into char and use decode functio for that one
example: decode (to_char(loc),'0',' ',to_char(loc))
decode (to_char(abc),'0',' ',to_char(abc))
now i use to sum of this it showing error.
sum(loc,abc) sum is number and loc,abc are charnow where i want to change in decode function or in sum function?
any another function?
[Updated on: Fri, 31 August 2007 01:03] Report message to a moderator |
|
|
|
| Re: char in to numaric? [message #263788 is a reply to message #263785] |
Fri, 31 August 2007 01:01  |
 |
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Post (copy and paste) what you did and an example of the data that give error.
By the way, which error?
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).
Regards
Michel
[Updated on: Fri, 31 August 2007 01:01] Report message to a moderator |
|
|
|
| Converting number to char problem? [message #263801 is a reply to message #263785] |
Fri, 31 August 2007 01:42 |
tondapi
Messages: 99
Registered: August 2007
Location: usa
|
Member |
|
|
Hi
I have another problem,
i have 2 number column loc,abc.i converted number into char and use decode functio for that one
example: decode (to_char(loc),'0',' ',to_char(loc))
decode (to_char(abc),'0',' ',to_char(abc))
now i use to sum of this it showing error.
sum(loc,abc) sum is number and loc,abc are charnow where i want to change in decode function or in sum function?
any another function?
|
|
|
|
|
|
|
|
| Re: Converting number to char problem? [message #263817 is a reply to message #263807] |
Fri, 31 August 2007 02:04 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I know you converted numbers to characters. But you didn't say why you did that.
And what's about abc = loc = 0?
Add two numbers:
SQL> define abc = 0
SQL> define loc = 0
SQL>
SQL> select &abc + &loc from dual;
0+0
----------
0
Add two zeros converted to characters (Oracle will implicitly convert them back to numbers)SQL> select to_char(&abc) + to_char(&loc) from dual;
TO_CHAR(0)+TO_CHAR(0)
---------------------
0
Use decode function as you'd want it to - zeros are converted to a SPACE character. What is sum of two space characters? Double space? What do YOU expect as a result here?
SQL> select decode(to_char(&abc), '0', ' ', &abc) +
2 decode(to_char(&loc), '0', ' ', &loc)
3 from dual;
select decode(to_char(0), '0', ' ', 0) +
*
ERROR at line 1:
ORA-01722: invalid number |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Converting number to char problem? [message #263866 is a reply to message #263848] |
Fri, 31 August 2007 03:39 |
tondapi
Messages: 99
Registered: August 2007
Location: usa
|
Member |
|
|
abc=number type list of values(0,1,2,3,4,......)
loc=number type(0,1,3,4,5,)
my user want to see null in place of 0,By using decode and to_char,converting number to char i do it.
Now my user want to see sum of abc and loc.
how can i do?
|
|
|
|
| Re: Converting number to char problem? [message #263871 is a reply to message #263866] |
Fri, 31 August 2007 03:47 |
 |
vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH
|
Senior Member |
|
|
| Quote: | example: decode (to_char(loc),'0',' ',to_char(loc))
decode (to_char(abc),'0',' ',to_char(abc))
now i use to sum of this it showing error.
sum(loc,abc) sum is number and loc,abc are charnow where i want to change in decode function or in sum function?
|
If at all loc is a number, why do you want to compare with a character (' ')?
(or)
| Quote: | loc and abc are number type and i converted as char type because of decode function
|
Do you think decode won't work for numbers?
By
Vamsi
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Converting number to char problem? [message #263953 is a reply to message #263946] |
Fri, 31 August 2007 06:11 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Well, DECODE is the way to return NULL instead of 0 (zero). Something like this:
SQL> set null NULL
SQL>
SQL> select decode(level - 1, 0, null, level - 1) result
2 from dual
3 connect by level <= 6;
RESULT
----------------------------------------
NULL
1
2
3
4
5
6 rows selected.
SQL>
It would help A LOT if you could post an example: input data set and desired output. Please, please, first read OraFAQ Forum Guide to learn how to properly format your message. Because, the way you do it is not acceptable (i.e. we can not understand what you, actually, want).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
