db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jakob Braeuchi <jbraeu...@gmx.ch>
Subject Re: [ReportQueryByCriteria] - Error in SQL generated when using GROUP BY sentence ?
Date Sat, 17 Jul 2004 20:53:56 GMT
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 ?

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


Mime
View raw message