db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Carlos Chávez <ccha...@agssa.net>
Subject [ReportQueryByCriteria] - Error in SQL generated when using GROUP BY sentence ?
Date Fri, 16 Jul 2004 18:11:23 GMT

  Hi to all,

  I write the next code :

  Criteria criterio = new Criteria();
  criterio.addEqualTo("fam_id", famId);
  criterio.addEqualTo("prod_id", prodId);
  ReportQueryByCriteria subQuery = new
ReportQueryByCriteria(Detallecompra.class, criterio);
  subQuery.setAttributes(new String[] { "max(facc_id)" });
  subQuery.addGroupBy(new String[] { "fam_id", "prod_id" });

  if the SQL generated above i want to use like a subquery,
  OJB send me the next message of error:

org.apache.ojb.broker.PersistenceBrokerSQLException:
java.sql.SQLException: ERROR: Subselect must have only one field

 Then, I was asking myself why this happened, if only set one attribute:
 subQuery.setAttributes(new String[] { "max(facc_id)" });

 I found that the attributes of GROUP BY are added to sentence SQL,
 although these do not appear in the result

 the SQL generated is:
 SELECT max(A0.FACC_ID),A0.FAM_ID as ojb_col_1,A0.PROD_ID as ojb_col_2
 FROM DETALLECOMPRA A0 WHERE (A0.FAM_ID = ?) AND A0.PROD_ID = ?
 GROUP BY A0.FAM_ID,A0.PROD_ID

 but i think the correct is :
 SELECT max(A0.FACC_ID)
 FROM DETALLECOMPRA A0 WHERE (A0.FAM_ID = ?) AND A0.PROD_ID = ?
 GROUP BY A0.FAM_ID,A0.PROD_ID

 because the attributes A0.FAM_ID,A0.PROD_ID are not indicated in the
 sentences: subQuery.setAttributes(new String[] { "max(facc_id)" });
 there are part of the GROUP BY but not the setAttributes sentences

 there is some reason for this behavior or this is an error ?

 IMO this is an error.

 i am using ojb 1.0.0.

 Cheers,

-- 
Carlos Chávez

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org


Mime
View raw message