ORA-01722: invalid number for a simple query

oracle numbers varchar ora-01722
Sasa picture Sasa · Feb 16, 2014 · Viewed 12.2k times · Source

I noticed the same topics here but I just couldn't find the answer because of my noob skill in databases, I tried a couple of answers but didn't work, apologies.

I have 5 tables and triggers for each of them if the user INSERT, UPDATE, DELETE

After I inserted the data into TRAFFIC, (date (varchar30), input_quant (varchar30), output_quant (varchar30), status (varchar30) )

I've tried a simple query

select sum (input_quant)status from traffic;

But the error pops out:

ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    
*Action:

Is it because of the type (VARCHAR)?

In the table TRAFFIC and SUPPLY I had to DISABLE the FKs so that I can insert the data, but now that I have inserted them, I can't ENABLE them (parent key not found). I don't think this has something to do with "INVALID NUMBER" error. But I'm still confused. What should I do?

Answer

dtam picture dtam · Feb 17, 2015

Try count() instead of sum(), sum is add number value for each of the input_quant values. count() is return number count of returned records.

Sum() works when the datatype is numeric

Related questions

oracle varchar to number

How do i convert a oracle varchar value to number eg table - exception exception_value 555 where exception_value is a varchar type I would like to test the value of exception_value column select * from exception where exception_value = 105 …

Read More
Why do i get #### in the NUMBER column after format... Oracle?

I have two problematic columns: Fee NUMBER type, AdjFee also NUMBER. After column Fee format a5; select Fee ID smth from Visit; i get Fee ID smth #### 123 klkl #### 654 rfjgr

Read More
Oracle NUMBER(p) storage size?

I've searched for it but i can't find a conclusive answer to my question... I need to know what is the storage size of a number(p) field in Oracle. Examples: NUMBER(1), NUMBER(3), NUMBER(8), NUMBER(10) etc...

Read More