The two problems, in my opinion, are that the return value of the subquery is the result of an aggregate function...
No simply the subqueries are not allowed in EJB-QL. Check the J2EE spec.
...and that in both queries the same table is accessed, so I think I cant use
FROM ..., IN(a.anotherTableDetails) f.e.
The IN keyword in the FROM clause is used for navigate over the CMR fields, not the CMP. Check the J2EE spec.
Perhaps try a DeclareSQL statement in JBoss to do what you want.
thx for your fast answer! :-)
Well, I read the Specs. But "not specified" does not mean "not supported by JBoss". That's the reason for JBossQL. But I didnt find some good description and especially some good examples in the JBoss Docs. Do you know some?
I know that the IN Operator within the FROM-Clause is used for CMR. That's why I said
I think I cant use FROM ..., IN(a.anotherTableDetails) f.e.
because a.anotherTableDetails would relate back to a in my case.
Or is it possible to define a Relation on itself?
Again, thx for your answer.
For JBOSS-QL, I have the JBoss Administration And Development guide which describes perfectly all the solutions for overriding EJB_QL limitations.
But JBOSS-QL do not support subqueries at all (just ORDER BY, parameters in IN and LIKE operators and UCASE/LCASE functions).
Of course you can define a self-relation for your table, but when I see your query, does it means that each item will be linked to all the others (because you want the max of all the entities) ? Needing an additionnal voluminous relation table ?
I don't think it is the good solution. OIf you want the max, perhaps you can use an ORDER BY clause taking the first in the collection.
But if you look at the DOCO, JBoss allows the DeclareSQL statement for unhandled FROM clauses. I never use it myself. I think the solution is there.
and thx again for your fast answer! :-)
Yes, getting an ordered collection and taking the last object is the way I go atm. But as I dont know exactly how many rows will be affected, I thought there might be a better solution.
I also have never used DeclaredSQL. But perhaps I should take a look at it!
If anyone who reads this post should have an example how to use DeclaredSQL in this special case, just post it! :-) And if someone also knows the XDoclet tags (because manual creation is not the right way! :-)), please also post it here.
Okay, I tried the DeclaredSQL and it seems that it might work. There was no exception at deployment, but I had no time to test it.
The XDoclet tag is @jboss.declared-sql
(look at http://xdoclet.sourceforge.net/xdoclet/index.html for further details)
But now I have a new problem: with DeclaredSQL it seems that I cant use aggregate functions in the SELECT-Clause. So it seems to work well if I only need columns.
Does anyone know a solution? (okay, I could create a PreparedStatement, but...)
i am using declared sql as i have to run subquery
my declared-sql query is like this :-
<query> <query-method> <method-name>findByMaxContactInfoId</method-name> <method-params/> </query-method> <declared-sql> <select> <field-name> CONTACTINFOID</field-name> <alias>o</alias> </select> <from>,RACONTACTINFO</from> <where> o.contactInfoId IN <select> <additional-columns>MAX(CONTACTINFOID) </additional-columns> <alias>g</alias> </select> <from>, RACONTACTINFO </from> </where> </declared-sql> </query> /code] i am using JBOSS 4.0.0 jdk 1.4 But showing following exception org.jboss.deployment.DeploymentException: The field-name element of declared-sql select is only allowed for ejbSelect queries how can i resolve this ?? even jboss 3.2.6 shows the same exception my original query is like this SELECT OBJECT(o) FROM RaContactInfo AS o WHERE o.contactInfoId IN (SELECT MAX(p.contactInfoId) FROM RaContactInfo AS p