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]
