0 Replies Latest reply on Jan 12, 2006 6:57 PM by krishna gajula

    EJB QL subquery for finder

    krishna gajula Newbie

      The following declared-SQL for customized finder throws an exception

      * @ejb.finder view-type="both"
      * signature="java.util.Collection findBillingInvoices(java.lang.Integer subscriptionId)"
      * query="Overridden by jbosscmp-jdbc.xml"
      * @jboss.declared-sql
      * signature="java.util.Collection findBillingInvoices(java.lang.Integer subscriptionId)"
      * distinct="true"
      * alias="i"
      * where="i.subscription.id={0} and i.type=2 and i.id = (SELECT MAX(i2.id) FROM InvoiceLineItem AS i2 where i2.subscription.id={0} )"

      Exception thrown....

      javax.ejb.FinderException: Find failed: java.sql.SQLException: ORA-00907: missing right parenthesis

      at org.jboss.ejb.plugins.cmp.jdbc.JDBCAbstractQueryCommand.execute(JDBCAbstractQueryCommand.java:223)

      I am using ...

      JBoss 4.0.1sp1
      Xdoclet 1.2.3
      oracle 9i

      The SQL seems to be correct. Is there anything I might be missing here? I also tried to replace jdbc thin driver (ojdbc14.jar) with latest version of 10g, but I am still getting same exception. Your help is greatly appreciated.