To_number function not work with decode function.

[marut junpueg]

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?

[Anup Batra]

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]

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]

Yes! It's work!
Thank you for your suggestion.