0

I'm new to JDOQL and I'm having troubles with the below. I'm trying to get the average salary for the department and then select the departments where the average salary is higher than a certain value. 

Query averageSalaryByDep = pm.newQuery(Employee.class);
averageSalaryByDep.setResult("department, avg(salary)");
averageSalaryByDep.setGrouping("department");

Query qry = pm.newQuery(Department.class);
qry.setFilter("this.name == dep.name && averageSalary > 10000");
qry.declareVariables("Department dep, double averageSalary");
qry.addSubquery(averageSalaryByDep, "Department dep, double averageSalary", null);

The error message I'm currently getting:

javax.jdo.JDOUserException: Class name averageSalary could not be resolved
    at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:636)
    at org.datanucleus.api.jdo.JDOQuery.executeInternal(JDOQuery.java:391)
    at org.datanucleus.api.jdo.JDOQuery.execute(JDOQuery.java:216)
Neil Stockton
  • 11,383
  • 3
  • 34
  • 29
Joanna
  • 54
  • 1
  • 1
  • 7

1 Answers1

1

A subquery has a single variable name (and returns a single thing). If unsure about something, put what the single-string query would look like (and the resultant SQL) and then it ought to be clear. The JDO spec has some useful examples IIRC

Regarding what you wanted to retrieve, I'd suggest you look at something more like

Query averageSalarySubq = pm.newQuery(Employee.class);
averageSalarySubq.setResult("avg(salary)");
averageSalarySubq.setFilter("this.department = :outerDepartment");

Query qry = pm.newQuery(Department.class);
qry.setFilter("averageSalary > 10000");
qry.declareVariables("double averageSalary");
qry.addSubquery(averageSalarySubq, "double averageSalary", null, "this");

which would equate to something like

SELECT FROM mydomain.Department WHERE 
    (SELECT AVG(e.salary) FROM mydomain.Employee e WHERE e.department = this) > 10000

hence subquery gets the average salary but joined to the outer query Department. Defining the SQL would reveal to you whether that is what you intend or not, but either way a subquery is for a single variable

Neil Stockton
  • 11,383
  • 3
  • 34
  • 29
  • Thanks. I saw yor comment earlier and was able to rewrite my code to something similar. I helped me a lot. – Joanna Apr 01 '17 at 16:59