CODEWITHSUNDEEP
sqloracle-databasetoad
Otto
Otto

Reputation: 11

Oracle SQL sum sometimes return double value

I met an interesting result when using TOAD on Oracle 11. The same query sometimes return the correct value, sometimes not. There is no group by. There is no join. Am I missing something?

The query is converting all digits to numeric value, then multiple 3 or multiple 1 depends on their location and then sum up.

The field is defined as "device_id VARCHAR2(25);"

Following query return 182, which is double the correct value 91. If change the condition as ii.device_id = '20637320050504', then it is correct value as 67. 

select sum(to_number(substr( ii. device_id, 1, 1 ))*3 +
               to_number(substr(ii. device_id, 13, 1 ))*3 + to_number(substr(ii. device_id, 12, 1 )) +
               to_number(substr(ii. device_id, 11, 1 ))*3 + to_number(substr(ii. device_id, 10, 1 )) +
               to_number(substr(ii. device_id, 9, 1 ))*3 + to_number(substr(ii. device_id, 8, 1 )) +
               to_number(substr(ii. device_id, 7, 1 ))*3 + to_number(substr(ii. device_id, 6, 1 )) +
               to_number(substr(ii. device_id, 5, 1 ))*3 + to_number(substr(ii. device_id, 4, 1 )) +
               to_number(substr(ii. device_id, 3, 1 ))*3 + to_number(substr(ii. device_id, 2, 1 )) ) as total
from identifier ii
where ii. device_id = '55019315060289'

I tried to modify the query out of my table definition as following, then the result is correct. 

select sum(to_number(substr( '55019315060289', 1, 1 ))*3 +
           to_number(substr('55019315060289', 13, 1 ))*3 +  to_number(substr('55019315060289', 12, 1 )) +
           to_number(substr('55019315060289', 11, 1 ))*3 +  to_number(substr('55019315060289', 10, 1 )) + 
           to_number(substr('55019315060289', 9, 1 ))*3 +   to_number(substr('55019315060289', 8, 1 )) +
           to_number(substr('55019315060289', 7, 1 ))*3 +   to_number(substr('55019315060289', 6, 1 )) +
           to_number(substr('55019315060289', 5, 1 ))*3 +   to_number(substr('55019315060289', 4, 1 )) +
           to_number(substr('55019315060289', 3, 1 ))*3 +   to_number(substr('55019315060289', 2, 1 )) ) as total 
from dual;

Looks like it is not TOAD or Oracle issue, but I could not figure out what I did wrong. Thanks.

Upvotes: 0

Views: 1096

Answers (1)

sql_dummy
sql_dummy

Reputation: 745

Make a note of "range of values that a datatype may support". The unsigned integer cannot hold the value 55019315060289,The max value that unsigned integer can hold is 2147483647

Upvotes: 1

Related Questions