OraFAQ Forum: SQL & PL/SQL » ORA-01426 "Numeric Overflow"

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01426 "Numeric Overflow"

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01426 "Numeric Overflow" [message #658777]

Tue, 27 December 2016 00:25

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

Report message to a moderator

Re: ORA-01426 "Numeric Overflow" [message #658778 is a reply to message #658777]

Tue, 27 December 2016 00:52

John Watson
Messages: 8928
Registered: January 2010
Location: Global Village

Senior Member

What's the problem?

Report message to a moderator

Re: ORA-01426 "Numeric Overflow" [message #658779 is a reply to message #658777]

Tue, 27 December 2016 01:01

ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Attachment: Screen Shot 2016-12-27 at 12.32.37 PM.png
(Size: 95.85KB, Downloaded 3675 times)

Report message to a moderator

Re: ORA-01426 "Numeric Overflow" [message #658780 is a reply to message #658779]

Tue, 27 December 2016 01:05

Littlefoot
Messages: 21807
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

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

Report message to a moderator

Re: ORA-01426 "Numeric Overflow" [message #658796 is a reply to message #658783]

Tue, 27 December 2016 07:32

Bill B
Messages: 1971
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.

Report message to a moderator

Previous Topic:	How to bypass the selective rows from SQL Analytic function
Next Topic:	ROLLBACK usingTRUNCATE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 17:42:07 CDT 2024