aspose file tools*
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


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
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
Author

To_number function not work with decode function.

marut junpueg
Greenhorn

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.
REPORT_DATE SOLID WATER FLAG
----------- ------ ------ -----
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> /
ERROR:
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.
eg
ur code :=
decode(wf.bsw_bottom_flag,'PLUS',(to_number(solid)+to_number(water)),water)
convert it like
decode(wf.bsw_bottom_flag,'PLUS',To_Char(to_number(solid)+to_number(water)),water)
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)
---------------------------------------------
abc99
ede100
dddfailed
eeefailed
ccc80

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

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.

basetti
marut junpueg
Greenhorn

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