Hi all

i have the following SQL Query:

SELECT DISTINCT TKAG.ILAUFNUMMER, TKAG.SKURSNR, TKAG.SREGION, TKAG.DKURSDATUM,
(SELECT count(*) FROM TKURSAGTEIL TKTE WHERE TKTE.FK_TKAG_LAUFNR = TKAG.ILAUFNUMMER) AS TEILE
FROM TKURSANGEBOT TKAG
WHERE ........

I was able to add Restrictions in the WHERE clause. This works fine. But now i would like to add the count subselect. How do i have to implement this with the Criteria API? Do i have to use DetachedCriteria or Projections?

Any help would be appreciated...

Kind Regards
Angela

Count is a projection. Projections.count(prop) is what you need. If you want to filter your query based on counts, then you'll need to use DetachedCriteria to wrap the projection into an subquery. Something likeThat'll get all instances of clazz that have the same "propToCount" as at most 4 other instances. (5 > (select ...))

Is there any way to accomplish this in version 2?

Would you use these same idioms to handle a counting sub-select within the FROM clause?

e.g.

SELECT a.infoA, b.infoB, c.itemCount
FROM TableA a, TableB b,
(SELECT count(*) FROM TableC WHERE c.infoA_ID) c
WHERE 1=1
AND a.infoA_ID = :qinfoA_ID
AND a.infoA_ID = b.infoA_FK(+)

From what I can tell, there is no way to accomplish either of these in V2 unless you run native SQL through SQLQuery. Am I wrong?

As far as I'm aware, there's no way to use criteria to put a subselect in the select or from clauses, only in the where clause.

You can use Restrictions.sqlRestrictions in Hibernate 2 or 3 to accomplish the same things as I laid out in my earlier post.

I also found another way to solve to problem. I jut put a formula into my hibernate mapping file:

<property name="kursTeileCount" formula="(SELECT count(*) FROM TKURSAGTEIL TKTE WHERE TKTE.FK_TKAG_LAUFNR = ILAUFNUMMER)"/>

Angela