---begin error---
net.sf.hibernate.QueryException: aggregate function expected before ( in SELECT [ from CMS_TRAVEL_BILLS in class edu.gettysburg.beans.TravelBill WHERE CMS_TRAVEL_BILLS.billId IN (SELECT DISTINCT(CMS_TRAVEL_BILLS.billId) FROM CMS_TRAVEL_BILLS in class edu.gettysburg.beans.TravelBill, CMS_TRAVEL_REQUESTS in class edu.gettysburg.beans.TravelRequest WHERE CMS_TRAVEL_BILLS.billId = CMS_TRAVEL_REQUESTS.billId AND CMS_TRAVEL_REQUESTS.shuttleDatetime > to_date('6 2009','MM YYYY')) ORDER BY BILL_ID ASC ]
---end error---

Notes: Using Oracle. Need a solution that does not require any Java coding, SQL only.

Problem: SELECT not supported in IN clause?

Query (hibernate):

SELECT *
FROM CMS_TRAVEL_BILLS in class edu.gettysburg.beans.TravelBill
WHERE CMS_TRAVEL_BILLS.billId
IN ( SELECT DISTINCT(CMS_TRAVEL_BILLS.billId)
FROM CMS_TRAVEL_BILLS in class edu.gettysburg.beans.TravelBill,
CMS_TRAVEL_REQUESTS in class edu.gettysburg.beans.TravelRequest
WHERE CMS_TRAVEL_BILLS.billId = CMS_TRAVEL_REQUESTS.billId
AND CMS_TRAVEL_REQUESTS.shuttleDatetime > to_date('6 2009','MM YYYY'))
ORDER BY CMS_TRAVEL_BILLS.billId ASC

Query (SQL):

SELECT *
FROM CMS_TRAVEL_BILLS in class edu.gettysburg.beans.TravelBill
WHERE bill_id
IN ( SELECT DISTINCT(b.bill_id)
FROM CMS_TRAVEL_BILLS b, CMS_TRAVEL_REQUESTS r
WHERE b.bill_id = r.bill_id
AND r.shuttled_datetime > to_date('6 2009','MM YYYY'))
ORDER BY bill_id ASC

Restraints: I can only append SQL after the text "SELECT * FROM CMS_TRAVEL_BILLS in class edu.gettysburg.beans.TravelBill" in our current code.

Thanks for any help, I've looked all over for a solution and couldn't find anything so far. There has to be a way to do this...I can't imagine something so common in SQL not being compatible.