Hi,

I have a user table, contains
userid, managerid

I have a resursive query written plsql

SELECT USR.USER_ID, USR.MANAGER_ID
FROM FBB_USER USR
WHERE USR.USER_ID NOT IN
( SELECT NVL(ALT.MANAGER_ID,0)

FROM FBB_USER ALT )
START WITH USR.USER_ID = '1'
CONNECT BY PRIOR USR.USER_ID = USR.MANAGER_ID ;

how can I translate this to HQL?

thanks

You can't. A hierarchical query like this requires either:

- procedural code, which SQL doesn't provide

or

- proprietary vendor extensions like CONNECT BY or SELECT RECURSIVE

which internally, are executed as a procedure with recursive semantics. You either have to write and call a stored procedure and call it with Hibernate 2.1 createSQLQuery() or fall use the proprietary vendor function in direct SQL, again with a createSQLQuery() or with a fallback to a SQL connection.

P.S. We are working on a solution for this problem, but it won't be ready soon.

Is there any word on this? Can you perhaps outline the gist of the proposed solution?

I'd also like to know the progress made on this issue. Is there a JIRA listing?