Starr Duskk
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!
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!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No, it's not an Ora 1466 error.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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.
in the column RISKRATING or RISKRATING is not numeric type.
The group function AVG works only over numbers.