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 Wed, 21 Jul 2004 00:46:16 GMT

 Hi Jakob.
 Thanks for the answer.

 But now a found the next problem with the next code:

 QueryByCriteria query = new QueryByCriteria(Product.class, criteria);
 query.addOrderBy("family.fam_name", true);
 query.addOrderBy("prod_name", true);

 This code generate the next error:
org.apache.ojb.broker.PersistenceBrokerException:
org.apache.ojb.broker.PersistenceBrokerSQLException:
java.sql.SQLException: ERROR: La relación "a1" no existe (The relation
"a1" does not exist)

 I found this is why the SQL is generated wrong, the SQL is:

 SELECT AO.PROD_ID, AO.PROD_NAME, <list of other fields>
 FROM PRODUCT A0
 ORDER BY A1.FAM_NAME, 2

 in this SQL is missing the clause INNER JOIN to the relationship FAMILY
 where the alias is A1.

 The sentences "query.addOrderBy("family.fam_name", true);" is not resolve
 correct.

 The same problem is happen with the group by.

 I think the correct SQL maybe is:
 SELECT AO.PROD_ID, AO.PROD_NAME, <list of other fields>
 FROM PRODUCT A0 INNER JOIN FAMILY A1 ON A0.FAM_ID = A1.FAM_ID
 ORDER BY A1.FAM_NAME, 2

 Cheers,
 Carlos Chávez.

Jakob Braeuchi Escribio :-)
> hi carlos,
>
> i just commited a fix to the groupBy-problem. please have a look at it.
>
> thanks
> jakob
>
> Carlos Chávez schrieb:
>
>>  Hi Jakob.
>>
>> Jakob Braeuchi Escribio :-)
>>
>>>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
>>
>>   This do not work in postgresql
>>
>>>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.
>>
>>
>>   group by index do not work in postgresql. work fine for order by but
>> not
>>   for group by.
>>
>>   Cheers,
>>   Carlos Chávez.
>>
>>
>>>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
>>>
>>
>>
>>
>
> ---------------------------------------------------------------------
> 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