the same issue has been reported by roger janssen. using a copy of criteria is imo the correct way. i'll fix it asap.
jakob
Dougall Squair wrote:
I've created a patch for this problem
The method
BrokerHelper.getQueryByCriteriaCount(QueryByCriteria aQuery)
now uses "getCriteria().copy()" instead of "getCriteria()" so that the new criteria does not include order by values.
Regards
Dougall
-----Original Message-----
From: Dougall Squair [mailto:[EMAIL PROTECTED]
Sent: Monday, 5 April 2004 2:40 PM
To: 'OJB Users List'
Subject: RE: [Bug] RC6 seems to be producing invalid SQL for Oracle when
u sing ReportQuerys
Ok,
I think I have tracked down the cause of this bug. It appears when you have
a ReportQuery that has a sort by clause then the method
org.apache.ojb.broker.util.BrokerHelper.getCountQuery(Query aQuery)
returns a Query that still has a sort by clause. This intern causes
org.apache.ojb.broker.accesslayer.sql.SqlQueryStatement.ensureColumns(List columns, List existingColumns, StringBuffer buf)
to append the sort by columns to the count query.
Should the method org.apache.ojb.broker.util.BrokerHelper.getCountQuery(Query aQuery) strip out any sort by columns ?
Regards
Dougall
-----Original Message-----
From: Dougall Squair [mailto:[EMAIL PROTECTED]
Sent: Monday, 5 April 2004 11:29 AM
To: 'OJB Users List'
Subject: [Bug] RC6 seems to be producing invalid SQL for Oracle when
using ReportQuerys
Hi ,
When I call
PersistenceBroker.getReportQueryIteratorByQuery(Query query)
The following SQL is being generated which is causing Oracle to throw a
SQLException. It appears that OJB is including the PK column when counting the number of
rows.
i.e. the SQL generated is select count(*), table.pk from table
which obviously causing a SQLException to be thrown
2004-04-05 11:18:46,170 DEBUG (org.apache) SQL:SELECT A0.mc_terms_agreement_id,A 0.mc_vendor_id,A0.status,A0.start_date,A1.vendor_name FROM mc_term_agreement A0, mc_vendor A1 WHERE A0.mc_vendor_id=A1.mc_vendor_id AND (A0.status <> ?) ORDER BY 1 2004-04-05 11:18:46,185 DEBUG (org.apache) SQL:SELECT count(*),A0.mc_terms_agree ment_id as ojb_col_1 FROM mc_term_agreement A0,mc_vendor A1 WHERE A0.mc_vendor_i d=A1.mc_vendor_id AND (A0.status <> ?) ORDER BY 2 2004-04-05 11:18:46,201 ERROR (org.apache) SQLException during the execution of the query (for a com.metcash.merchandising.model.agreement.Agreement): ORA-00937 : not a single-group group function
java.sql.SQLException: ORA-00937: not a single-group group function
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289) at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579)
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
------------------------------------------------------------------------
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
