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 Re: [ReportQueryByCriteria] - Error in SQL generated when using GROUP BY sentence ?
Date Tue, 20 Jul 2004 17:19:50 GMT

 Hi jakob.

Jakob Braeuchi Escribio :-)
> hi carlos,
>
> this is caused by the call to ensureColumns in
> SqlSelectStatement#getStatement
>
> ...
>              groupByFields = query.getGroupBy();
>              if (groupByFields != null && !groupByFields.isEmpty())
>              {
> //                ensureColumns(groupByFields, columnList, stmt);
>              }
> ...
>
> if you comment this line (nr. 221) the sql should look ok.
>
> imo we could drop this call completely, but before i do so, i'll have to
> run all
> the testcases.
> we use the same procedure for orderby as well, because we
> order
> by column-position and not by column-name. may be we could drop this as
> well ?

  i think we can drop this in both case.

  if the particular implementation of a database need this, we can do this
  for the database only.

  In case of postgresql i test this and we do not need what the column
  in the "group by" an "order by" are part of the columns of the result.

  Cheers
  Carlos Chávez.

>
> hth
> jakob
>
> Carlos Chávez schrieb:
>
>>   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,
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
>


-- 
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