13 Replies Latest reply on Mar 14, 2014 8:38 AM by rockeys sh

    Can I use nested query in ejb finders?

    Amit Mujawar Newbie

      We need to use following query in CMP entity bean:

      SELECT OBJECT(o) FROM CPRecoverLog o WHERE o.status = ?1 AND o.CPTransactionId = ( SELECT MIN(o1.CPTransactionId) FROM CPRecoverLog o1 WHERE o1.status = ?1)

      The bean tags are as follows:

      * @ejb.bean description = "CPRecoverLog"
      * name = "CPRecoverLog"
      * local-jndi-name = "abs.CPRecoverLog"
      * type = "CMP"
      * view-type = "local"
      * primkey-field = "id"
      * cmp-version = "2.x"
      * schema = "CPRecoverLog"
      *
      * @jboss.persistence table-name = "CPRecoverLog"
      *
      * @ejb.pk class = "java.lang.Integer"
      *
      * @jboss.unknown-pk
      * column-name = "id"
      * jdbc-type = "INTEGER"
      * sql-type = "INTEGER"
      * class = "java.lang.Integer"
      *
      * @jboss.entity-command name = "sybase-fetch-key"
      * class = "org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCSybaseCreateCommand"
      * @jboss.entity-command-attribute name = "pk-sql"
      * value = "SELECT @@IDENTITY"
      *
      * @ejb.finder
      * signature = "Collection findAll()"
      * query = "SELECT OBJECT(o) FROM CPRecoverLog o WHERE o.id IS NOT NULL"
      *
      * @ejb.finder
      * signature = "Collection findByStatus(java.lang.String status)"
      * query = "SELECT OBJECT(o) FROM CPRecoverLog o WHERE o.status = ?1"
      *
      * @ejb.finder
      * signature = "Collection findByStatusUnique(java.lang.String status)"
      * query = "SELECT OBJECT(o) FROM CPRecoverLog o WHERE o.status = ?1 AND o.CPTransactionId = (SELECT MIN(o1.CPTransactionId) FROM CPRecoverLog o1 WHERE o1.status = ?1)"
      *


      When I try to deploy this bean I get a parse exception for the finder - findByStatusUnique.
      Is it because EJB-QL does not support nested queries?
      I also tried the JBoss-QL. But even after that the problem persisted.
      Am I doing something wrong or am I missing something ?
      I know there are other ways to do this using ejbFinder methods, but I want to use the finders without having to code any JDBC.

      Does anybody know how to work with such nested queries in finders/

      Regards,
      Amit

        • 1. Re: Can I use nested query in ejb finders?
          Amit Mujawar Newbie

          Note that the same query was working in Weblogc 8.1
          The exception I get in JBoss 4.0.1 SP1 is -

          2005-04-27 17:34:25,170 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCSybaseCreateCommand.CPRecoverLog] Insert Entity SQL: INSERT INTO CPRecoverLog (cp_transaction_id, attemptCount, status, type, object, timeEntered, timeUpdated) VALUES (?, ?, ?, ?, ?, ?, ?)
          2005-04-27 17:34:25,170 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCCommandFactory.CPRecoverLog] entity-command: [commandName=sybase-fetch-key,commandClass=class org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCSybaseCreateCommand,attributes={pk-sql=SELECT @@IDENTITY}]
          2005-04-27 17:34:25,186 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCRemoveEntityCommand.CPRecoverLog] Remove SQL: DELETE FROM CPRecoverLog WHERE id=?
          2005-04-27 17:34:25,186 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.CPRecoverLog] Table not create as requested: CPRecoverLog
          2005-04-27 17:34:25,186 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCFindByPrimaryKeyQuery.CPRecoverLog#findByPrimaryKey] SQL: SELECT t0_CPRecoverLog.id FROM CPRecoverLog t0_CPRecoverLog WHERE t0_CPRecoverLog.id=?
          2005-04-27 17:34:25,186 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCQueryManager.CPRecoverLog] Added findByPrimaryKey query command for local home interface
          2005-04-27 17:34:25,201 DEBUG [org.jboss.ejb.plugins.cmp.jdbc.JDBCJBossQLQuery.CPRecoverLog#findByStatusUnique] JBossQL: SELECT OBJECT(o) FROM CPRecoverLog o WHERE o.status = ?1 AND o.CPTransactionId = (SELECT MIN(o1.CPTransactionId) FROM CPRecoverLog o1 WHERE o1.status = ?1)
          2005-04-27 17:34:25,295 INFO [STDOUT] org.jboss.ejb.plugins.cmp.ejbql.ParseException: Encountered "SELECT" at line 1, column 83.
          Was expecting one of:
          "CONCAT" ...
          "SUBSTRING" ...
          "LCASE" ...
          "UCASE" ...
          "(" ...
          <STRING_LITERAL> ...
          <STRING_VALUED_PARAMETER> ...
          <STRING_VALUED_PATH> ...

          2005-04-27 17:34:25,311 INFO [STDOUT] at org.jboss.ejb.plugins.cmp.ejbql.JBossQLParser.generateParseException(JBossQLParser.java:5614)
          2005-04-27 17:34:25,326 INFO [STDOUT] at org.jboss.ejb.plugins.cmp.ejbql.JBossQLParser.jj_consume_token(JBossQLParser.java:5495)
          2005-04-27 17:34:25,326 INFO [STDOUT] at org.jboss.ejb.plugins.cmp.ejbql.JBossQLParser.StringExpression(JBossQLParser.java:2086)
          2005-04-27 17:34:25,326 INFO [STDOUT] at org.jboss.ejb.plugins.cmp.ejbql.JBossQLParser.StringPrimary(JBossQLParser.java:2105)
          2005-04-27 17:34:25,326 INFO [STDOUT] at org.jboss.ejb.plugins.cmp.ejbql.JBossQLParser.StringExpression(JBossQLParser.java:2079)
          2005-04-27 17:34:25,342 INFO [STDOUT] at org.jboss.ejb.plugins.cmp.ejbql.JBossQLParser.ComparisonExpression(JBossQLParser.java:1489)
          2005-04-27 17:34:25,342 INFO [STDOUT] at org.jboss.ejb.plugins.cmp.ejbql.JBossQLParser.SimpleCondExpression(JBossQLParser.java:969)
          2005-04-27 17:34:25,342 INFO [STDOUT] at org.jboss.ejb.plugins.cmp.ejbql.JBossQLParser.ConditionalPrimary(JBossQLParser.java:882)
          2005-04-27 17:34:25,342 INFO [STDOUT] at org.jboss.ejb.plugins.cmp.ejbql.JBossQLParser.ConditionalTest(JBossQLParser.java:850)
          2005-04-27 17:34:25,357 INFO [STDOUT] at org.jboss.ejb.plugins.cmp.ejbql.JBossQLParser.ConditionalFactor(JBossQLParser.java:827)
          2005-04-27 17:34:25,357 INFO [STDOUT] at org.jboss.ejb.plugins.cmp.ejbql.JBossQLParser.WhereConditionalTerm(JBossQLParser.java:686)
          2005-04-27 17:34:25,357 INFO [STDOUT] at org.jboss.ejb.plugins.cmp.ejbql.JBossQLParser.WhereExpression(JBossQLParser.java:629)
          2005-04-27 17:34:25,357 INFO [STDOUT] at org.jboss.ejb.plugins.cmp.ejbql.JBossQLParser.WhereClause(JBossQLParser.java:593)
          2005-04-27 17:34:25,357 INFO [STDOUT] at org.jboss.ejb.plugins.cmp.ejbql.JBossQLParser.EJBQL(JBossQLParser.java:126)
          2005-04-27 17:34:25,357 INFO [STDOUT] at org.jboss.ejb.plugins.cmp.ejbql.JBossQLParser.parse(JBossQLParser.java:73)
          2005-04-27 17:34:25,373 INFO [STDOUT] at org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLCompiler.compileJBossQL(JDBCEJBQLCompiler.java:194)
          2005-04-27 17:34:25,373 INFO [STDOUT] at org.jboss.ejb.plugins.cmp.jdbc.JDBCJBossQLQuery.(JDBCJBossQLQuery.java:43)
          2005-04-27 17:34:25,373 INFO [STDOUT] at org.jboss.ejb.plugins.cmp.jdbc.JDBCCommandFactory.createJBossQLQuery(JDBCCommandFactory.java:72)
          2005-04-27 17:34:25,373 INFO [STDOUT] at org.jboss.ejb.plugins.cmp.jdbc.JDBCQueryManager.start(JDBCQueryManager.java:260)
          2005-04-27 17:34:25,373 INFO [STDOUT] at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.startStoreManager(JDBCStoreManager.java:490)
          2005-04-27 17:34:25,389 INFO [STDOUT] at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.start(JDBCStoreManager.java:381)
          2005-04-27 17:34:25,389 INFO [STDOUT] at org.jboss.ejb.plugins.CMPPersistenceManager.start(CMPPersistenceManager.java:157)
          2005-04-27 17:34:25,389 INFO [STDOUT] at org.jboss.ejb.EntityContainer.startService(EntityContainer.java:340)
          2005-04-27 17:34:25,389 INFO [STDOUT] at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:272)
          2005-04-27 17:34:25,389 INFO [STDOUT] at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBeanSupport.java:222)
          2005-04-27 17:34:25,404 INFO [STDOUT] at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
          2005-04-27 17:34:25,404 INFO [STDOUT] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          2005-04-27 17:34:25,404 INFO [STDOUT] at java.lang.reflect.Method.invoke(Method.java:324)
          2005-04-27 17:34:25,404 INFO [STDOUT] at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:144)
          2005-04-27 17:34:25,420 INFO [STDOUT] at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
          2005-04-27 17:34:25,420 INFO [STDOUT] at org.jboss.mx.server.Invocation.invoke(Invocation.java:72)
          2005-04-27 17:34:25,420 INFO [STDOUT] at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:249)
          2005-04-27 17:34:25,420 INFO [STDOUT] at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:642)
          2005-04-27 17:34:25,420 INFO [STDOUT] at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:891)
          2005-04-27 17:34:25,436 INFO [STDOUT] at $Proxy0.start(Unknown Source)
          2005-04-27 17:34:25,436 INFO [STDOUT] at org.jboss.system.ServiceController.start(ServiceController.java:416)
          2005-04-27 17:34:25,436 INFO [STDOUT] at sun.reflect.GeneratedMethodAccessor8.invoke(Unknown Source)
          2005-04-27 17:34:25,436 INFO [STDOUT] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          2005-04-27 17:34:25,436 INFO [STDOUT] at java.lang.reflect.Method.invoke(Method.java:324)
          2005-04-27 17:34:25,436 INFO [STDOUT] at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:144)
          2005-04-27 17:34:25,451 INFO [STDOUT] at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
          2005-04-27 17:34:25,451 INFO [STDOUT] at org.jboss.mx.server.Invocation.invoke(Invocation.java:72)
          2005-04-27 17:34:25,451 INFO [STDOUT] at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:249)
          2005-04-27 17:34:25,451 INFO [STDOUT] at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:642)
          2005-04-27 17:34:25,451 INFO [STDOUT] at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
          2005-04-27 17:34:25,467 INFO [STDOUT] at $Proxy48.start(Unknown Source)
          2005-04-27 17:34:25,467 INFO [STDOUT] at org.jboss.ejb.EjbModule.startService(EjbModule.java:394)
          2005-04-27 17:34:25,467 INFO [STDOUT] at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:272)
          2005-04-27 17:34:25,467 INFO [STDOUT] at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBeanSupport.java:222)
          2005-04-27 17:34:25,482 INFO [STDOUT] at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
          2005-04-27 17:34:25,482 INFO [STDOUT] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          2005-04-27 17:34:25,482 INFO [STDOUT] at java.lang.reflect.Method.invoke(Method.java:324)
          2005-04-27 17:34:25,482 INFO [STDOUT] at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:144)
          2005-04-27 17:34:25,482 INFO [STDOUT] at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
          2005-04-27 17:34:25,482 INFO [STDOUT] at org.jboss.mx.server.Invocation.invoke(Invocation.java:72)
          2005-04-27 17:34:25,498 INFO [STDOUT] at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:249)
          2005-04-27 17:34:25,498 INFO [STDOUT] at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:642)
          2005-04-27 17:34:25,498 INFO [STDOUT] at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:891)
          2005-04-27 17:34:25,498 INFO [STDOUT] at $Proxy0.start(Unknown Source)
          2005-04-27 17:34:25,498 INFO [STDOUT] at org.jboss.system.ServiceController.start(ServiceController.java:416)
          2005-04-27 17:34:25,498 INFO [STDOUT] at sun.reflect.GeneratedMethodAccessor8.invoke(Unknown Source)
          2005-04-27 17:34:25,514 INFO [STDOUT] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          2005-04-27 17:34:25,514 INFO [STDOUT] at java.lang.reflect.Method.invoke(Method.java:324)
          2005-04-27 17:34:25,514 INFO [STDOUT] at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:144)
          2005-04-27 17:34:25,514 INFO [STDOUT] at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
          2005-04-27 17:34:25,529 INFO [STDOUT] at org.jboss.mx.server.Invocation.invoke(Invocation.java:72)
          2005-04-27 17:34:25,529 INFO [STDOUT] at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:249)
          2005-04-27 17:34:25,529 INFO [STDOUT] at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:642)
          2005-04-27 17:34:25,529 INFO [STDOUT] at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
          2005-04-27 17:34:25,529 INFO [STDOUT] at $Proxy22.start(Unknown Source)
          2005-04-27 17:34:25,545 INFO [STDOUT] at org.jboss.ejb.EJBDeployer.start(EJBDeployer.java:605)
          2005-04-27 17:34:25,545 INFO [STDOUT] at org.jboss.deployment.MainDeployer.start(MainDeployer.java:964)
          2005-04-27 17:34:25,545 INFO [STDOUT] at org.jboss.deployment.MainDeployer.start(MainDeployer.java:956)
          2005-04-27 17:34:25,545 INFO [STDOUT] at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:775)
          2005-04-27 17:34:25,545 INFO [STDOUT] at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:738)
          2005-04-27 17:34:25,545 INFO [STDOUT] at sun.reflect.GeneratedMethodAccessor25.invoke(Unknown Source)
          2005-04-27 17:34:25,545 INFO [STDOUT] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          2005-04-27 17:34:25,561 INFO [STDOUT] at java.lang.reflect.Method.invoke(Method.java:324)
          2005-04-27 17:34:25,561 INFO [STDOUT] at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:144)
          2005-04-27 17:34:25,561 INFO [STDOUT] at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
          2005-04-27 17:34:25,561 INFO [STDOUT] at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:122)
          2005-04-27 17:34:25,576 INFO [STDOUT] at org.jboss.mx.server.Invocation.invoke(Invocation.java:74)
          2005-04-27 17:34:25,576 INFO [STDOUT] at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:131)
          2005-04-27 17:34:25,576 INFO [STDOUT] at org.jboss.mx.server.Invocation.invoke(Invocation.java:74)
          2005-04-27 17:34:25,576 INFO [STDOUT] at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:249)
          2005-04-27 17:34:25,576 INFO [STDOUT] at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:642)
          2005-04-27 17:34:25,592 INFO [STDOUT] at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
          2005-04-27 17:34:25,592 INFO [STDOUT] at $Proxy8.deploy(Unknown Source)
          2005-04-27 17:34:25,592 INFO [STDOUT] at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:305)
          2005-04-27 17:34:25,592 INFO [STDOUT] at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:481)
          2005-04-27 17:34:25,592 INFO [STDOUT] at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:204)
          2005-04-27 17:34:25,592 INFO [STDOUT] at org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(AbstractDeploymentScanner.java:277)
          2005-04-27 17:34:25,592 INFO [STDOUT] at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:272)
          2005-04-27 17:34:25,607 INFO [STDOUT] at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBeanSupport.java:222)
          2005-04-27 17:34:25,607 INFO [STDOUT] at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
          2005-04-27 17:34:25,607 INFO [STDOUT] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          2005-04-27 17:34:25,607 INFO [STDOUT] at java.lang.reflect.Method.invoke(Method.java:324)
          2005-04-27 17:34:25,623 INFO [STDOUT] at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:144)
          2005-04-27 17:34:25,623 INFO [STDOUT] at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
          2005-04-27 17:34:25,623 INFO [STDOUT] at org.jboss.mx.server.Invocation.invoke(Invocation.java:72)
          2005-04-27 17:34:25,623 INFO [STDOUT] at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:249)
          2005-04-27 17:34:25,639 INFO [STDOUT] at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:642)
          2005-04-27 17:34:25,639 INFO [STDOUT] at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:891)
          2005-04-27 17:34:25,639 INFO [STDOUT] at $Proxy0.start(Unknown Source)
          2005-04-27 17:34:25,639 INFO [STDOUT] at org.jboss.system.ServiceController.start(ServiceController.java:416)
          2005-04-27 17:34:25,654 INFO [STDOUT] at sun.reflect.GeneratedMethodAccessor8.invoke(Unknown Source)
          2005-04-27 17:34:25,654 INFO [STDOUT] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          2005-04-27 17:34:25,654 INFO [STDOUT] at java.lang.reflect.Method.invoke(Method.java:324)
          2005-04-27 17:34:25,654 INFO [STDOUT] at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:144)
          2005-04-27 17:34:25,654 INFO [STDOUT] at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
          2005-04-27 17:34:25,654 INFO [STDOUT] at org.jboss.mx.server.Invocation.invoke(Invocation.java:72)
          2005-04-27 17:34:25,670 INFO [STDOUT] at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:249)
          2005-04-27 17:34:25,670 INFO [STDOUT] at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:642)
          2005-04-27 17:34:25,670 INFO [STDOUT] at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
          2005-04-27 17:34:25,670 INFO [STDOUT] at $Proxy4.start(Unknown Source)
          2005-04-27 17:34:25,670 INFO [STDOUT] at org.jboss.deployment.SARDeployer.start(SARDeployer.java:261)
          2005-04-27 17:34:25,670 INFO [STDOUT] at org.jboss.deployment.MainDeployer.start(MainDeployer.java:964)
          2005-04-27 17:34:25,686 INFO [STDOUT] at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:775)
          2005-04-27 17:34:25,686 INFO [STDOUT] at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:738)
          2005-04-27 17:34:25,686 INFO [STDOUT] at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:722)
          2005-04-27 17:34:25,686 INFO [STDOUT] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
          2005-04-27 17:34:25,701 INFO [STDOUT] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
          2005-04-27 17:34:25,701 INFO [STDOUT] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          2005-04-27 17:34:25,701 INFO [STDOUT] at java.lang.reflect.Method.invoke(Method.java:324)
          2005-04-27 17:34:25,701 INFO [STDOUT] at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:144)
          2005-04-27 17:34:25,717 INFO [STDOUT] at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
          2005-04-27 17:34:25,717 INFO [STDOUT] at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:122)
          2005-04-27 17:34:25,717 INFO [STDOUT] at org.jboss.mx.server.Invocation.invoke(Invocation.java:74)
          2005-04-27 17:34:25,717 INFO [STDOUT] at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:131)
          2005-04-27 17:34:25,717 INFO [STDOUT] at org.jboss.mx.server.Invocation.invoke(Invocation.java:74)
          2005-04-27 17:34:25,717 INFO [STDOUT] at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:249)
          2005-04-27 17:34:25,732 INFO [STDOUT] at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:642)
          2005-04-27 17:34:25,732 INFO [STDOUT] at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
          2005-04-27 17:34:25,732 INFO [STDOUT] at $Proxy5.deploy(Unknown Source)
          2005-04-27 17:34:25,732 INFO [STDOUT] at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:413)
          2005-04-27 17:34:25,732 INFO [STDOUT] at org.jboss.system.server.ServerImpl.start(ServerImpl.java:310)
          2005-04-27 17:34:25,732 INFO [STDOUT] at org.jboss.Main.boot(Main.java:162)
          2005-04-27 17:34:25,748 INFO [STDOUT] at org.jboss.Main$1.run(Main.java:423)
          2005-04-27 17:34:25,748 INFO [STDOUT] at java.lang.Thread.run(Thread.java:536)
          2005-04-27 17:34:25,748 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.parentTraceEnabled=true
          2005-04-27 17:34:25,764 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.nestedTraceEnabled=false
          2005-04-27 17:34:25,764 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.detectDuplicateNesting=true

          • 2. Re: Can I use nested query in ejb finders?
            Franco Apprentice

            Hi,
            I think that ejb 2.x do not support subqueries.
            You may try with jbossql.

            • 3. Re: Can I use nested query in ejb finders?
              Amit Mujawar Newbie

              Hi,
              Actually I had tried with JBoss-QL also. But its giving same problem.
              Do you know how to use subqueries as mentioned in my original post ?

              Regards
              Amit

              • 4. Re: Can I use nested query in ejb finders?
                Franco Apprentice

                Hello,

                have you tried <declared-sql> ?

                Ciao, Franco

                • 5. Re: Can I use nested query in ejb finders?
                  Amit Mujawar Newbie

                  Not yet. I could not figure out how to construct the declare-sql clauses.
                  Can you help me out?
                  The query is not trivial .
                  SELECT OBJECT(o) FROM CPRecoverLog o WHERE o.status = ?1 AND o.CPTransactionId = ( SELECT MIN(o1.CPTransactionId) FROM CPRecoverLog o1 WHERE o1.status = ?1)

                  • 6. Re: Can I use nested query in ejb finders?
                    Franco Apprentice

                    Hi,
                    try (cannot test if works):

                    <declared-sql>

                    o

                    WHERE o.status = {0} AND o.CPTransactionId = ( SELECT MIN(o1.CPTransactionId) FROM CPRecoverLog o1 WHERE o1.status = {0})

                    </declared-sql>

                    But do you relly need a subquery ?

                    Hope this helps

                    • 7. Re: Can I use nested query in ejb finders?
                      Franco Apprentice

                      Sorry...


                      <declared-sql>
                      <select>
                      <alias>o</alias>
                      </select>
                      <where>WHERE o.status = {0} AND o.CPTransactionId = ( SELECT MIN(o1.CPTransactionId) FROM CPRecoverLog o1 WHERE o1.status = {0})</where>
                      <order></order>
                      </declared-sql>



                      • 8. Re: Can I use nested query in ejb finders?
                        Franco Apprentice

                        reSorry

                        <declared-sql>
                         <select>
                         <alias>o</alias>
                         </select>
                         <where>WHERE o.status = {0} AND o.CPTransactionId = ( SELECT MIN(o1.CPTransactionId) FROM CPRecoverLog o1 WHERE o1.status = {0})</where>
                         <order></order>
                        </declared-sql>
                        
                        


                        • 9. Re: Can I use nested query in ejb finders?
                          Amit Mujawar Newbie

                          That was so nice of you!
                          I will try it out. Well, I need to use the subquery since the logic demands it. If this declared-sql does not work I will move to jdbc and fire plain sql to get the pk of the record and then move on with rest of the code after the finder.
                          Thanks a lot again.

                          • 10. Re: Can I use nested query in ejb finders?
                            Amit Mujawar Newbie

                            Thanks a lot. Its working fine.
                            Regards
                            Amit

                            • 11. Re: Can I use nested query in ejb finders?
                              Tim Newbie

                              Hi did the declared-sql work???

                              Cheers

                              • 13. Re: Can I use nested query in ejb finders?
                                rockeys sh Newbie

                                Hi Amit,

                                 

                                I new to jboss. I am trying to deploy my ear file in JBOSS 4.0.5 and i got the same exception above which you got. Iam trying to change my code but not sure how to change that . Can you please share the code changes which is after change. so that i can have look at that.

                                 

                                Thanks in advance.

                                 

                                Rockeys