6 Replies Latest reply on Jan 14, 2009 4:46 AM by Sachin Jain

    How to use Subqueries in a finder-Method???

    Johannes Konstantinidis Newbie

      Hello,

      I would like to create a finder-Method with a query like the following:

      SELECT Object(a) FROM myTable AS a WHERE a.date = (SELECT MAX(b.date) FROM myTable AS b)

      But when I make it this way I always get a ParseException.

      The two problems, in my opinion, are that the return value of the subquery is the result of an aggregate function and that in both queries the same table is accessed, so I think I cant use
      FROM ..., IN(a.anotherTableDetails) f.e.

      Thx for help or ideas in advance.

      Lachdanan

        • 1. Re: How to use Subqueries in a finder-Method???
          sesques Novice

           

          "konstantinidis" wrote:
          Hello,
          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.

          "konstantinidis" wrote:
          Hello,
          ...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.

          Pascal



          • 2. Re: How to use Subqueries in a finder-Method???
            Johannes Konstantinidis Newbie

            Hello,

            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.

            Lachdanan

            • 3. Re: How to use Subqueries in a finder-Method???
              sesques Novice

              Hi Lachdanan,

              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.

              Good luck
              pascal

              • 4. Re: How to use Subqueries in a finder-Method???
                Johannes Konstantinidis Newbie

                Hello,

                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.

                Thx

                Lachdanan

                • 5. Re: How to use Subqueries in a finder-Method???
                  Johannes Konstantinidis Newbie

                  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...)

                  Lachdanan

                  • 6. Re: How to use Subqueries in a finder-Method???
                    Sachin Jain Newbie

                    Hello sir
                    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