Home » SQL & PL/SQL » SQL & PL/SQL » Query Resulting in Numeric Overflow (Oracle 11g)
Query Resulting in Numeric Overflow [message #594080] Fri, 23 August 2013 04:57 Go to next message
raj_te
Messages: 23
Registered: August 2013
Location: INDIA
Junior Member
I am writing a query and the query is resulting in Numeric Overflow.

SELECT

(power((Trlg_5Yr_Anl_Ror_Pt/100+1),5)-1)*100 AS Trlg_5Yr_Cum_Ror_Pt

FROM Cdp
where ....
and .....



In this above query,the power function is throwing error. Since the value in the column "Trlg_5Yr_Anl_Ror_Pt" is very big,applying power function is throwing Numeric Error. What can I do to handle this?

IN TABLE Cdp,column "Trlg_5Yr_Anl_Ror_Pt" is defined as NUMBER. Will Changin it to FLOAT resolve the issue. Please suggest.
Re: Query Resulting in Numeric Overflow [message #594107 is a reply to message #594080] Fri, 23 August 2013 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What can I do to handle this?


Just add "AND Trlg_5Yr_Anl_Ror_Pt < <the maximum possible value before the error>".

Regards
Michel
Re: Query Resulting in Numeric Overflow [message #594108 is a reply to message #594107] Fri, 23 August 2013 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or, in SELECT, "CASE WHEN Trlg_5Yr_Anl_Ror_Pt < <the maximum possible value before the error> THEN 'Overflow' ELSE TO_CHAR(<your expression>) END".

Regards
Michel
Re: Query Resulting in Numeric Overflow [message #594122 is a reply to message #594108] Fri, 23 August 2013 13:23 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1850
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel sir,

It is also possible to keep multiple CASE statements, each CASE WHEN will handle the maximum limit. The number of CASE statements would be the total digits divided by the maximum limit imposed by Oracle.
Re: Query Resulting in Numeric Overflow [message #594124 is a reply to message #594122] Fri, 23 August 2013 13:26 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't see what you mean or what does this add to my previous post. I don't see why there could be several CASE when there is only ONE expression.

Regards
Michel
Re: Query Resulting in Numeric Overflow [message #594126 is a reply to message #594124] Fri, 23 August 2013 13:50 Go to previous messageGo to next message
Lalit Kumar B
Messages: 1850
Registered: May 2013
Location: World Wide on the Web
Senior Member
I mean, if one CASE overflows to the ELSE, and if ELSE also overflows, then multiple CASE statements need to be used and the the final ELSE will handle the remaining numeric overflow(however, in every case as well as in else it has to be kept within the limit)
Re: Query Resulting in Numeric Overflow [message #594127 is a reply to message #594126] Fri, 23 August 2013 14:03 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the relation with the current case?
And if the Oracle database is DB2? And if the server crashes? And if I turn blind?
Don't talk of is not in the topic. We can add as many as "and if" we can imagine but this has nothing to do with the question.
And do NOT answer to this it is useless. This is the final point.

Regards
Michel
Re: Query Resulting in Numeric Overflow [message #594154 is a reply to message #594127] Sat, 24 August 2013 01:40 Go to previous messageGo to next message
raj_te
Messages: 23
Registered: August 2013
Location: INDIA
Junior Member
Michel..

The case statement is not getting evaluated. i tried the way you told, but the above query is not able to retrieve the results. Until the result is obtained, i cannot put it across the case statement. I wrote the below.

SELECT

CASE WHEN ((power((Trlg_5Yr_Anl_Ror_Pt/100+1),5)-1)*100 ) > 1200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
THEN "OVER FLOW"
ELSE
(power((Trlg_5Yr_Anl_Ror_Pt/100+1),5)-1)*100
END AS Trlg_5Yr_Cum_Ror_Pt

FROM Cdp
where ....
and .....


Please note that incase I dont multiply the power result by 100 then everything is coming correct. I mean to say
if I remove *100 from the power((Trlg_5Yr_Anl_Ror_Pt/100+1),5)-1)*100 ) then result is evaluating correctly. in short if i write

power((Trlg_5Yr_Anl_Ror_Pt/100+1),5)-1) ) everything is coming properly.

Re: Query Resulting in Numeric Overflow [message #594155 is a reply to message #594080] Sat, 24 August 2013 01:43 Go to previous messageGo to next message
raj_te
Messages: 23
Registered: August 2013
Location: INDIA
Junior Member
Yes Lalit, its going over 38 significant digit. The maximum possible value which its taking is 12E125
Re: Query Resulting in Numeric Overflow [message #594156 is a reply to message #594155] Sat, 24 August 2013 01:53 Go to previous messageGo to next message
raj_te
Messages: 23
Registered: August 2013
Location: INDIA
Junior Member
Michel,

Even adding "AND Trlg_5Yr_Anl_Ror_Pt < <the maximum possible value before the error>". is not resulting. please see below:

create table Test1 (a number)

insert into test1 values (9989899898)

select * from test1

select power(a,13) from test1
where a < 987946979401072000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000.00
Re: Query Resulting in Numeric Overflow [message #594158 is a reply to message #594154] Sat, 24 August 2013 02:14 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
raj_te wrote on Sat, 24 August 2013 08:40
Michel..

The case statement is not getting evaluated. i tried the way you told, but the above query is not able to retrieve the results. Until the result is obtained, i cannot put it across the case statement. I wrote the below.

SELECT

CASE WHEN ((power((Trlg_5Yr_Anl_Ror_Pt/100+1),5)-1)*100 ) > 1200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
THEN "OVER FLOW"
ELSE
(power((Trlg_5Yr_Anl_Ror_Pt/100+1),5)-1)*100
END AS Trlg_5Yr_Cum_Ror_Pt

FROM Cdp
where ....
and .....


This is NOT what I posted.

Regards
Michel

Re: Query Resulting in Numeric Overflow [message #594159 is a reply to message #594156] Sat, 24 August 2013 02:16 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
please see below:


What does this intend to show?

Please CAREFULLY read what I posted.
Please Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data.
Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

Regards
Michel
Re: Query Resulting in Numeric Overflow [message #594163 is a reply to message #594159] Sat, 24 August 2013 02:36 Go to previous messageGo to next message
raj_te
Messages: 23
Registered: August 2013
Location: INDIA
Junior Member
Can you explain what you meant by writing this;


Or, in SELECT, "CASE WHEN Trlg_5Yr_Anl_Ror_Pt < <the maximum possible value before the error> THEN 'Overflow' ELSE TO_CHAR(<your expression>) END".

Regards
Michel
Re: Query Resulting in Numeric Overflow [message #594164 is a reply to message #594163] Sat, 24 August 2013 02:44 Go to previous messageGo to next message
raj_te
Messages: 23
Registered: August 2013
Location: INDIA
Junior Member
Just add "AND Trlg_5Yr_Anl_Ror_Pt < <the maximum possible value before the error>".

Regards
Michel


For this comment i posted a very simple example showing that even the above is not working.

1--Creating a table
create table Test1 (a number)

2--inserting a row
insert into test1 values (9989899898)

3--selecting the row
select * from test1

4--Selecting row with power function applied
select power(a,13) from test1
where a < 987946979401072000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000.00



if you see, 4 shows exactly what u have written to do. Please let me know what else you need and if anything is not clear for you.
Re: Query Resulting in Numeric Overflow [message #594168 is a reply to message #594164] Sat, 24 August 2013 03:15 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"the maximum possible value before the error" means "the maximum possible value before the error FOR YOUR VALUE, not for the result of your expression; that is for "a" not for "power(a,13)".
So you have to search the min value for a where power(a,13) returns overflow.

Regards
Michel
Re: Query Resulting in Numeric Overflow [message #594207 is a reply to message #594168] Mon, 26 August 2013 02:32 Go to previous messageGo to next message
raj_te
Messages: 23
Registered: August 2013
Location: INDIA
Junior Member
Thanks Michel..Its working Fine..Appreciate your help.
Re: Query Resulting in Numeric Overflow [message #594208 is a reply to message #594207] Mon, 26 August 2013 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So now post the final code to help the future readers.

Regards
Michel
Re: Query Resulting in Numeric Overflow [message #594284 is a reply to message #594208] Tue, 27 August 2013 04:06 Go to previous messageGo to next message
raj_te
Messages: 23
Registered: August 2013
Location: INDIA
Junior Member
Sure,,,The final query goes like

SELECT

CASE WHEN Trlg_5Yr_Anl_Ror_Pt > 99999999999999
THEN "OVER FLOW"
ELSE
(power((Trlg_5Yr_Anl_Ror_Pt/100+1),5)-1)*100
END AS Trlg_5Yr_Cum_Ror_Pt

FROM Cdp
where ....
and .....
Re: Query Resulting in Numeric Overflow [message #594306 is a reply to message #594284] Tue, 27 August 2013 04:51 Go to previous message
Michel Cadot
Messages: 58605
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Previous Topic: Redefinition For Move Tablespace.
Next Topic: Remote statment
Goto Forum:
  


Current Time: Mon Jul 28 16:16:45 CDT 2014

Total time taken to generate the page: 0.09457 seconds