File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/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 Android Security Essentials Live Lessons this week in the Android 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.
 
Don't get me started about those stupid light bulbs.
 
subject: To_number function not work with decode function.
 
Similar Threads
compatibility of date format
Configure websphere with remote IIS
Fetching Max value from NVARCHAR column
Adding new resource adapter in Websphere ND gives error for node agent and node sync
floating point in javascript