Maybe I'm doing something very poor, but I thought I had used these
constructs successfully before. (This is Oracle, BTW.)
My RESOURCE_LOCK table schema:
<table name="RESOURCE_LOCK">
<column name="ITEM_ID" size="27" type="DECIMAL" javaName="ItemID"/>
<column name="RESOURCE_DESCRIPTOR" primaryKey="true"
required="true" size="100" type="VARCHAR"
javaName="ResourceDescriptor"/>
<column name="ENTRY_TIMESTAMP" type="TIMESTAMP"
javaName="EntryTimestamp"/>
<column name="LOCKER_ID" size="27" type="DECIMAL" javaName="LockerID"/>
<column name="LOCKER_TYPE" size="100" type="VARCHAR"
javaName="LockerType"/>
<column name="RESOURCE_TYPE" size="27" type="DECIMAL"
javaName="ResourceTypeID"/>
</table>
My code:
Criteria crit = new Criteria();
crit.addGroupByColumn(ResourceLockPeer.LOCKER_TYPE);
List<ResourceLock> locks = ResourceLockPeer.doSelect(crit);
The observed SQL from log4j when turned on:
SELECT RESOURCE_LOCK.ITEM_ID, RESOURCE_LOCK.RESOURCE_DESCRIPTOR,
RESOURCE_LOCK.ENTRY_TIMESTAMP, RESOURCE_LOCK.LOCKER_ID,
RESOURCE_LOCK.LOCKER_TYPE, RESOURCE_LOCK.RESOURCE_TYPE FROM DNS.RESOURCE_LOCK
GROUP BY RESOURCE_LOCK.LOCKER_TYPE
The exception:
org.apache.torque.TorqueException: java.sql.SQLException: ORA-00979: not a
GROUP BY expression
DNS is the torque.database.schema, but I don't understand why it is
inconsistent in adding the database prefix in the FROM clause but not
the GROUP clause.
Joins are similarly affected:
Criteria crit = new Criteria();
crit.add(ResourceLockPeer.LOCKER_ID, new Long(2115));
crit.add(ResourceLockPeer.LOCKER_TYPE, "RECURBILLINGPROC");
crit.addJoin(ResourceLockPeer.RESOURCE_DESCRIPTOR, RecurBillingPeer.ID);
crit.addGroupByColumn(RecurBillingPeer.ACCOUNT_ID);
crit.addGroupByColumn(RecurBillingPeer.ACCOUNT_PAYMENT_ID);
List<RecurBilling> recBilling = RecurBillingPeer.doSelect(crit);
The SQL is even worse, as it contains both the tablename and dbprefix.tablename
in the where clause, which gives Oracle confusion fits.
SELECT <..all RECUR_BILLING fields..> FROM RESOURCE_LOCK, RECUR_BILLING,
DNS.RECUR_BILLING, DNS.RESOURCE_LOCK WHERE
RESOURCE_LOCK.RESOURCE_DESCRIPTOR=RECUR_BILLING.ID AND
RESOURCE_LOCK.LOCKER_TYPE='RECURBILLINGPROC' AND RESOURCE_LOCK.LOCKER_ID=2115
GROUP BY RECUR_BILLING.ACCOUNT_ID, RECUR_BILLING.ACCOUNT_PAYMENT_ID
Brendan
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
