Link to home
Start Free TrialLog in
Avatar of Starr Duskk
Starr DuskkFlag for United States of America

asked on

AVG - Average - Unable to Read data

I am trying to average a column:

SELECT     AVG(RISKRATING) AS EXPR1
FROM         TBL_COMPNT_REVW_RISK
WHERE     (REVIEWID = '12345-04-07') AND (RISKLIKELIHOOD <> 0) AND (RISKIMPACT <> 0)

I get the error: <unable to read data>


There are simply 9 rows with these values:
2,3,2,3,3,1,3,2,1

how can I get the average of this column without this error?

thanks!

Avatar of schwertner
schwertner
Flag of Antarctica image

This possibly means that you have corrupted numeric data
in the column RISKRATING or RISKRATING is not numeric type.
The group function AVG works only over numbers.
SOLUTION
Avatar of Franck Pachot
Franck Pachot
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Starr Duskk

ASKER

I showed you exactly what is in that column.
there is no corrupted data. it is a numeric column.
 
If I change them all to 3's, then it returns 3.
3,3,3,3,3,3
If I ch ange it to: 2,3,3,3,3,3
or: 2,2,3,3,3,3
it chokes.
If I change it to:
2,2,2,3,3,3
it returns 2.50
In short, oracle stinks at averaging.
How do others 'fix" this?
Was this an ORA-01466 error?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
No, it's not an Ora 1466 error.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,
The Decimal datatype has a precision of 28, and it fails in overflow with oracle numbers that can be 38.
The solution is here (use getOracle Number):
http://msdn.microsoft.com/en-us/library/cc716726.aspx
Regards,
Franck.

toad works differently, sql navigator works differently, pl/sql developer works different and similarly visual studio 2008 as well..

you should always try any sql query when investigating for any errors in sqlplus atleast once to understand whether it is really related to database sql query or something to do with frontend interface applications to interact with the database.
i mean to say .. you cannot say this "In short, oracle stinks at averaging." if you test from any front end interface application.