Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 971
  • Last Modified:

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!

0
Starr Duskk
Asked:
Starr Duskk
  • 3
  • 2
  • 2
  • +2
4 Solutions
 
schwertnerCommented:
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.
0
 
Franck PachotCommented:
Hi,
Is it the oracle error message 'ORA-01466: unable to read data - table definition has changed' ?
If it is a client message, then try to get the oracle message. Rune the query from sqlplus, for example.
Regards,
Franck.
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
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?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
awking00Commented:
Was this an ORA-01466 error?
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
If I change it to:
SELECT     ROUND(AVG(RISKRATING), 8) AS EXPR1
It works.
I had to add the ROUND()
In Toad, it displayed:  
2.66666667
But retreiving the value from a output parameter or running it in a query window in visual studio 2008 choked.
My output parameter is:

TotalRiskRating = IntegerCheckNull(CType(dr("TotalRiskRating"), System.Decimal))
What should I have there? Will decimal work now with the ROUND? Guess I can find out by trying it.
 
0
 
Starr DuskkASP.NET VB.NET DeveloperAuthor Commented:
No, it's not an Ora 1466 error.
0
 
awking00Commented:
If Toad displays the correct answer, then the function is not the problem, it's whatever is using the output parameter limiting the number of decimals in some fashion. I would think the round function limiting the number of decimals should work. You might try different levels of rounding to determine what the limit might be.
0
 
schwertnerCommented:
We can comment how it works on plain SQL*Plus and I am sure it works.
Other interfaces can give undesired results.
Possibly the trick would be to return the AVG value as a string - use TO_CHAR function and after that to convert it to number. Many interfaces use different number formats and it is very importnt to know which number format is the needed Oracle number format.
0
 
Franck PachotCommented:
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.

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
i mean to say .. you cannot say this "In short, oracle stinks at averaging." if you test from any front end interface application.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now