Hello! I am afraid you can't do that in a single JPQL statement - currently JPQL allows subqueries only in WHERE and HAVING clauses. Maybe we will get something more in JPA 2.0...
Regards, Milosz > Hello, > > I am looking for some assistance in converting a pretty complex SQL > statement involving subqueries to JPQL. I am looking to use a constructor > statement so I can return multiple values (essentially most of an entity > plus a few values), but am at a loss as to how to connect the pieces. > > > SELECT NEW entity.helper.LeftConfList(conf.title, > COUNT(cnr) >, COUNT(cn) >) > > > > > FROM Conference conf, > WHERE conf.courseSectionUID = ?1 > AND conf.deleted <> 1 ORDER BY conf.displaySequence > > > The original (working) SQL is: > > SELECT conf.CONFERENCE_TITLE, (SELECT COUNT(*) FROM CONFERENCE_NOTES cn, > LAST_EDIT le > WHERE cn.U_OBJECT_ID NOT IN ( > SELECT cn.U_OBJECT_ID > FROM CONFERENCE_NOTES cn, UNREAD ur > WHERE cn.U_OBJECT_ID = ur.READ_OBJECT_ID > AND cn.U_CONFERENCE_ID = conf.U_OBJECT_ID > AND ur.U_USER_ID = ?1) > AND cn.U_CONFERENCE_ID = conf.U_OBJECT_ID > AND le.DELETED <> 1 AND cn.U_OBJECT_ID = le.U_OBJECT_ID) AS unread_count, > > (SELECT COUNT (*) > FROM CONFERENCE_NOTES cn, LAST_EDIT le > WHERE cn.U_CONFERENCE_ID = conf.U_OBJECT_ID > AND le.DELETED <> 1 AND cn.U_OBJECT_ID = le.U_OBJECT_ID) AS total_count > > FROM CONFERENCES conf, LAST_EDIT le > WHERE conf.U_COURSE_SECTION_ID = ?2 > AND le.DELETED <> 1 AND conf.U_OBJECT_ID = le.U_OBJECT_ID ORDER BY > conf.DISPLAY_SEQUENCE > > Any help would be greatly appreciated. > > Thanks. > > > -- > View this message in context: > http://n2.nabble.com/Subqueries-in-JPQL-tp1301743p1301743.html > Sent from the OpenJPA Users mailing list archive at Nabble.com. > >
