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 Tue, 20 Jul 2004 18:52:54 GMT
hi carlos,

i have a solution that works for hsqldb and mysql.

if the orderBy- or groupByFiled is found in the list of selected fields it's 
index is used otherwise it's name is used:

SELECT A0.ID,A0.FIRSTNAME,count(*) FROM PERSON A0 WHERE A0.FIRSTNAME LIKE ? 
GROUP BY 1,2

SELECT max(A0.ID) FROM PERSON A0 GROUP BY A0.LASTNAME,A0.FIRSTNAME

could you please verify whether group by index also is working for postgresql.

jakob

Carlos Chávez schrieb:

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

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