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
JDK1.5.0_06
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.