Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01426 "Numeric Overflow"
icon5.gif  ORA-01426 "Numeric Overflow" [message #658777] Tue, 27 December 2016 00:25 Go to next message
challa384
Messages: 2
Registered: December 2016
Junior Member
Hi All,

I am a noob to SQL. I am trying to do some calculations using SQL code.

update table_name
set col1=1.018*141*power(col2*0.01131/0.7, -0.329) * power(0.993,col3)
where col4='F'
and col2<=61.9;

Please help me resolve this. Thanks in Advance
Re: ORA-01426 "Numeric Overflow" [message #658778 is a reply to message #658777] Tue, 27 December 2016 00:52 Go to previous messageGo to next message
John Watson
Messages: 7265
Registered: January 2010
Location: Global Village
Senior Member
What's the problem?
Re: ORA-01426 "Numeric Overflow" [message #658779 is a reply to message #658777] Tue, 27 December 2016 01:01 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
/forum/fa/13375/0/
Re: ORA-01426 "Numeric Overflow" [message #658780 is a reply to message #658779] Tue, 27 December 2016 01:05 Go to previous messageGo to next message
Littlefoot
Messages: 21367
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It appears that the result goes into infinity. Is the formula correct? Do COL2 and COL3 look all right (I mean, do their values make sense in this context)?

The POWER (n2, n1) says (quoting Oracle doc.): POWER returns n2 raised to the n1 power. The base n2 and the exponent n1 can be any numbers, but if n2 is negative, then n1 must be an integer.

Therefore, as - in the first POWER function n1 is not an integer, then COL2 shouldn't be negative. Can you check COL2's sign?

As of COL3, it can be (virtually) any positive number (as that POWER returns 0 in that case), but it can't be much less than -1E4, so - check its range in the TABLE_NAME.

[Updated on: Tue, 27 December 2016 01:05]

Report message to a moderator

Re: ORA-01426 "Numeric Overflow" [message #658783 is a reply to message #658780] Tue, 27 December 2016 01:25 Go to previous messageGo to next message
challa384
Messages: 2
Registered: December 2016
Junior Member
Thanks for the reply Littlefoot. There were some rows in col2 where the values were 0 and hence the error. Deleted the 0's and now the code is running.

Regards,
Challa
Re: ORA-01426 "Numeric Overflow" [message #658796 is a reply to message #658783] Tue, 27 December 2016 07:32 Go to previous message
Bill B
Messages: 1718
Registered: December 2004
Senior Member
you should not store calculate values that can be gotten from the other columns. it is too easy to get out of sync if you change one of the columns used in the calculation. If your are running oracle 11 or above I would make the calculation as a virtual column containing the calculation and a constraint to stop col2 and col3 from being zero or null.
Previous Topic: How to bypass the selective rows from SQL Analytic function
Next Topic: ROLLBACK usingTRUNCATE
Goto Forum:
  


Current Time: Sun Feb 25 06:50:44 CST 2018

Total time taken to generate the page: 0.01482 seconds