• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

To_number function not work with decode function.

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes! It's work!
Thank you for your suggestion.
 
reply
    Bookmark Topic Watch Topic
  • New Topic