Home » SQL & PL/SQL » SQL & PL/SQL » Character to number (merged)
Character to number (merged) [message #263785] Fri, 31 August 2007 00:53 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #263803 is a reply to message #263801] Fri, 31 August 2007 01:47 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Excuse me?

Punctuation exists. Use it!!!

What are you saying, actually? Two columns are numbers. Now you are converting them to characters and summing them? Why? What's wrong with summing two numbers?

By the way, what do you expect as a result of SUM function when both 'loc' and 'abc' are 0 (zero)?
Re: Converting number to char problem? [message #263807 is a reply to message #263803] Fri, 31 August 2007 01:50 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
loc and abc are number type and i converted as char type because of decode function.

now i want to do sum of abc and loc ? sum is number and loc and abc are char type?
Re: Converting number to char problem? [message #263817 is a reply to message #263807] Fri, 31 August 2007 02:04 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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 #263822 is a reply to message #263817] Fri, 31 August 2007 02:14 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
it show error boss
Re: Converting number to char problem? [message #263826 is a reply to message #263822] Fri, 31 August 2007 02:20 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Of course it does! Simplified, it was
SQL> select ' ' + ' ' from dual;
select ' ' + ' ' from dual
       *
ERROR at line 1:
ORA-01722: invalid number
This is what you are doing here. Once again: what result do you expect as a result of
' ' + ' '
Re: Converting number to char problem? [message #263828 is a reply to message #263826] Fri, 31 August 2007 02:27 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
select sum(char+char) from dual;
it showing error. Here sum is Number.

number(char+char) from dual;
Re: Converting number to char problem? [message #263831 is a reply to message #263828] Fri, 31 August 2007 02:32 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm sorry, I'm afraid I don't understand you (and vice versa). However, I'm sure someone else will be able to help you.
Re: Converting number to char problem? [message #263848 is a reply to message #263831] Fri, 31 August 2007 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Until he understands what is datatype, parameter datatype and (implicit) conversions, I'm afraid nobody can help him.

Regards
Michel
Re: Converting number to char problem? [message #263866 is a reply to message #263848] Fri, 31 August 2007 03:39 Go to previous messageGo to next message
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 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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 #263874 is a reply to message #263871] Fri, 31 August 2007 03:53 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
iam writing my decode function

decode(loc,0,' ',loc) from emp;

0 is number and ' ' is char for that i use to_char
Re: Converting number to char problem? [message #263877 is a reply to message #263866] Fri, 31 August 2007 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
my user want to see null in place of 0

decode(loc,0,to_number(null),loc)

Quote:
my user want to see sum of abc and loc

abc+loc

Regards
Michel
Re: Converting number to char problem? [message #263899 is a reply to message #263877] Fri, 31 August 2007 04:24 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
it showing invalid number?
Re: Converting number to char problem? [message #263900 is a reply to message #263899] Fri, 31 August 2007 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It can't if the datatypes are the ones you said.

Regards
Michel
Re: Converting number to char problem? [message #263903 is a reply to message #263899] Fri, 31 August 2007 04:33 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
it showing invalid number?
Is it a statement or a question?

Do you want to ask as the following?
Quote:
Will it give the error (invalid number)?
(or) Do you want to tell us that it is giving error?

Quote:
decode(loc,0,' ',loc) from emp;
Still you didn't answer my question.
Quote:
If at all loc is a number, why do you want to compare with a character (' ')?
I mean you are putting a space (' '). Do you want to compare with null ('')?
If so, it is better to use to_number(null) as Michel suggested.

By
Vamsi
Re: Converting number to char problem? [message #263913 is a reply to message #263903] Fri, 31 August 2007 05:01 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
i CHANGE LOC INTO NUMBER AND APPLYING DECODE FUNCTION

DECODE(LOC,0,TO_NUMBER('NULL'),LOC) FROM EMP;

BUT IT SHOWING ERROR LIKE THIS INVALID NUMBER
Re: Converting number to char problem? [message #263919 is a reply to message #263913] Fri, 31 August 2007 05:03 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Don't use Capitals.
Quote:
DECODE(LOC,0,TO_NUMBER('NULL'),LOC) FROM EMP;
Quote:
decode(loc,0,to_number(null),loc)
I can see the difference.

Didn't you?

By
Vamsi
Re: Converting number to char problem? [message #263930 is a reply to message #263919] Fri, 31 August 2007 05:23 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
thanks boss its working
Re: Converting number to char problem? [message #263931 is a reply to message #263930] Fri, 31 August 2007 05:25 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Cool Cool
Anyway have a look at OraFAQ Forum Guide.

By
Vamsi
Re: Converting number to char problem? [message #263932 is a reply to message #263919] Fri, 31 August 2007 05:29 Go to previous messageGo to next message
tondapi
Messages: 99
Registered: August 2007
Location: usa
Member
by using this function result is
1
2
3
4
5
before result is
0
1
2
3
4
5 i want to see blankspace there. how ican do it?
the result is

1
2
3
4
5 like this ?


[Updated on: Fri, 31 August 2007 05:40]

Report message to a moderator

Re: Converting number to char problem? [message #263936 is a reply to message #263932] Fri, 31 August 2007 05:33 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Are you waiting for acknowledgement? Razz

Edit: Complete your question.
By
Vamsi

[Updated on: Fri, 31 August 2007 05:33]

Report message to a moderator

Re: Converting number to char problem? [message #263946 is a reply to message #263932] Fri, 31 August 2007 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And format your post.

Regards
Michel
Re: Converting number to char problem? [message #263953 is a reply to message #263946] Fri, 31 August 2007 06:11 Go to previous message
Littlefoot
Messages: 20901
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>
Now that we know how to do it, back to your arithmetic question.

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).
Previous Topic: creating sequences
Next Topic: Like Pattern search
Goto Forum:
  


Current Time: Sat Dec 10 22:44:06 CST 2016

Total time taken to generate the page: 0.05534 seconds