custom finder problem

[redhonda]

I would like define a custom finder in a cmp entity bean that returns the largest id.

The table MY_TABLE has these 2 fields:
id integer
something varchar


In my home cmpBeanHome, I have declared this method:

public cmpBean findMaxID() throws RemoteException, FinderException;


Then, in my jaws.xml, I added:

<enterprise-beans>


...


<ejb-name>cmpBean</ejb-name>

findMaxID
MY_TABLE.id = (select max(MY_TABLE.id) from MY_TABLE group by MY_TABLE.id)



</enterprise-beans>

...



When calling findMaxID(), the error I got was

javax.servlet.ServletException: Find failed, msg=java.sql.SQLException: Column not found: ID in statement [SELECT MY_TABLE.id FROM MY_TABLE where MY_TABLE.id = (select max(MY_TABLE.id) from MY_TABLE group by MY_TABLE.id)]

Why did it complain about the column "id"?

Also, on the documentation, there is very little explanation on having custom finders in the bean itself, instead of in jaws.xml. Has anyone tried this before?

[redhonda]

I have spent a lot of time on this problem. Anyone has any insights?

Thanks

[shogun]

I think you have to remove MY_TABLE befor the column names, because you are working only into this table, called MY_TABLE and you aren't using other tables (Joins).

Maybe you can also remove the id = ... . Try it.

[davidjencks]

did you try running the generated query in an sql tool? I dont think your subquery makes any sense, and I think the table names are 100% ambiguous.

Group by normally needs to be a non-aggregated field present in the result set. max(x) ... group by x doesn't make any sense.

try

MY_TABLE.id = (select max(a.id) from MY_TABLE a)