It's not a secret anymore!*
The moose likes Oracle/OAS and the fly likes To_number function not work with decode function. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "To_number function not work with decode function." Watch "To_number function not work with decode function." New topic

To_number function not work with decode function.

marut junpueg

Joined: Jul 08, 2002
Posts: 2
Hi all
I have a problem when use decode with to_number function.
This is example data. All column except report date have charactor type.
----------- ------ ------ -----
02-AUG-2001 Trace ND WATER
01-AUG-2001 WATER
03-AUG-2001 Trace 0.15 WATER
05-AUG-2001 0.05 0.05 PLUS
07-AUG-2001 Trace 0.05 WATER
09-AUG-2001 Trace ND WATER
11-AUG-2001 Trace ND WATER
I want to plus SOLID and WATER when both are numeric that I check by FLAG and use this code.
decode(wf.bsw_bottom_flag,'PLUS',to_number(solid)+to_number(water),water) N1
But they return error
SQL> /
ORA-01722: invalid number
What wrong I do?

Best regards<br />Marut
Anup Batra
Ranch Hand

Joined: Jan 21, 2002
Posts: 41
Hi Marut,
The error is because u r using decode to give two different value of different data type in a column. A column can have only one data type, and by decode u are forcing it to put number as well as varchar datatype value into one column which is not permitted.
To fix this problem u should enclose the to_number derived value into to_char function and by this u are converting the number value into character value and will work fine.
ur code :=
convert it like
I hope so this will solve the problem.
One interesting problem I think I should give. I won't give hint but it is related to ur problem
table is like
NAME(varchar) Marks(number column)
-------------------- ---------------------abc 99
ede 100
ddd null
eee null
ccc 80

give the output as
NAME(varchar) Marks(number column)

See the changes in ddd and eee.
This will clear.
Anup Batra
s basetti

Joined: Jun 16, 2002
Posts: 14
i don't think that works too..
Error shows somewhere around you have char type & converting it into to_number...check again.
check where solid+water is not of number type.

marut junpueg

Joined: Jul 08, 2002
Posts: 2
Yes! It's work!
Thank you for your suggestion.
I agree. Here's the link:
subject: To_number function not work with decode function.