sql query problem

[jagang]

hi,

I try now several hours to execute the following sql query within Repository.SQLQuery(sql) Method:

SELECT AVG(Stereotypes) AS AVGStereotypes FROM (SELECT c.Package_ID, COUNT(c.Stereotype) AS Stereotypes FROM t_object c GROUP BY c.Package_ID HAVING COUNT(c.Stereotype) > 0)

I just want to get the average count of stereotypes for a package within the project. The SQL Syntax should be right but I always get the error: DAO.QueryDef[3131] Syntax error in FROM clause.

Even if I try such simple sql queries like "SELECT Package_ID FROM ( SELECT Package_ID FROM t_package )" or "SELECT a.Package_ID FROM ( SELECT Package_ID FROM t_package ) a" I receive the same error message.

Is there something I did wrong?

greetings alex

[Geert Bellekens]

Yes, Access (and especially the DAO implementation used to access Access) doesn't like anything fancy like subqueries.
Why do use these subqueries at all?
What's wrong with "select Package_ID from t_package" iso "SELECT Package_ID FROM ( SELECT Package_ID FROM t_package )"?

Geert

[OpenIT Solutions]

Stereotype is a text field in t_object so you would need to do a unique count of stereotype per package then average the result. The sql would look something like this:

SELECT avg (package.packageCount) FROM (SELECT count (stereotype) AS packageCount from t_object group by stereotype, package_id) AS package

However this gives an error - not sure why, ill post again when i figure it out...

[OpenIT Solutions]

Sorry mis read your sql - which as you say should work - not sure what the issue is...