Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01426 "Numeric Overflow"
ORA-01426 "Numeric Overflow" Tue, 27 December 2016 00:25
 challa384 Messages: 2Registered: 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;```

Re: ORA-01426 "Numeric Overflow" [message #658778 is a reply to message #658777] Tue, 27 December 2016 00:52
 John Watson Messages: 8547Registered: 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
Re: ORA-01426 "Numeric Overflow" [message #658780 is a reply to message #658779] Tue, 27 December 2016 01:05
 Littlefoot Messages: 21670Registered: June 2005 Location: Croatia, Europe Senior MemberAccount 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: 2Registered: 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
 Bill B Messages: 1971Registered: 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: Wed May 12 13:23:40 CDT 2021