I have the following native SQL:
"select {usr}.USERID as {usr.userId},
{usr}.USERNAME as {usr.username},
{usr}.PASSWORD as {usr.password},
{usr}.EFFECTIVEDATE as {usr.effectiveDate},
{usr}.ENDDATE as {usr.endDate},
from FMUSER AS {usr}
where {usr}.USERNAME=:?"
Which I thought is followed Hibernate2 Reference Documentation 8.3.6 example. However, while running the code, I got the following error message:
R net.sf.hibernate.QueryException: unexpected token: as [select {usr}.USERID as {usr.userId},{usr}.USERNAME as {usr.username}, {usr}.PASSWORD as {usr.password}, {usr}.EFFECTIVEDATE as {usr.effectiveDate}, {usr}.ENDDATE as {usr.endDate}, from FMUSER AS {usr} where {usr}.USERNAME=:?]

Does any one can help me?
Thanks
George

are you using createSQLQuery() to execute this ?
You should ;)

Please show code and stacktrace when asking for this ...see more at:
http://www.hibernate.org/160.html

Here it is:
Hibernate version 2.1
Version of database: Oracle 9
My mapping documents:
<class name="data.User" table="FMUSER">

<id name="userId" unsaved-value="null" >
<column name="USERID" sql-type="NUMBER" not-null="true"/>
<generator class="sequence"/>
</id>

<!-- A cat has to have a name, but it shouldn' be too long. -->
<property name="username">
<column name="USERNAME" sql-type="varchar(50)" not-null="true"/>
</property>

<property name="password">
<column name="PASSWORD" sql-type="char(16)" />
</property>

<property name="effectiveDate" type="java.util.Date">
<column name="EFFECTIVEDATE" sql-type="date" />
</property>

<property name="endDate" type="java.util.Date">
<column name="ENDDATE" sql-type="date" />
</property>

</class>

</hibernate-mapping>


Java code:
String userName="myName";Error Message:

String sql="select {usr}.USERID as {usr.userId},{usr}.USERNAME as {usr.username}, {usr}.PASSWORD as {usr.password}, {usr}.EFFECTIVEDATE as {usr.effectiveDate}, {usr}.ENDDATE as {usr.endDate} from FMUSER AS {usr} where {usr}.USERNAME=:username";

User user = null;
Query query = session.createSQLQuery(sql, "usr", data.User.class);
query.setParameter("username", userName);
user = (User) query.uniqueResult();
return user;


[12/9/03 15:40:39:281 EST] 1936405f SystemOut O Hibernate: select usr.USERID as USERID0_,usr.USERNAME as USERNAME0_, usr.PASSWORD as PASSWORD0_, usr.EFFECTIVEDATE as EFFECTIV4_0_, usr.ENDDATE as ENDDATE0_ from FMUSER AS usr where usr.USERNAME=?
[12/9/03 15:40:39:624 EST] 1936405f JDBCException W net.sf.hibernate.util.JDBCExceptionReporter SQL Error: 933, SQLState: 42000
[12/9/03 15:40:39:624 EST] 1936405f JDBCException E net.sf.hibernate.util.JDBCExceptionReporter ORA-00933: SQL command not properly ended

[12/9/03 15:40:39:640 EST] 1936405f JDBCException W net.sf.hibernate.util.JDBCExceptionReporter SQL Error: 933, SQLState: 42000
[12/9/03 15:40:39:656 EST] 1936405f JDBCException E net.sf.hibernate.util.JDBCExceptionReporter ORA-00933: SQL command not properly ended

[12/9/03 15:40:39:656 EST] 1936405f JDBCException E net.sf.hibernate.util.JDBCExceptionReporter SQLException occurred
[12/9/03 15:40:39:718 EST] 1936405f JDBCException E net.sf.hibernate.util.JDBCExceptionReporter TRAS0014I: The following exception was logged java.sql.SQLException: ORA-00933: SQL command not properly ended

at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:662)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:214)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:135)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:227)
at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:72)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:190)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:406)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:57)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:461)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:414)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:614)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:190)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:406)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:57)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:461)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:414)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:614)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:57)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:461)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:414)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:614)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:57)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:461)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:414)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:614)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:343)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:592)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:614)
.
java.sql.SQLException: ORA-00933: SQL command not properly ended

at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:662)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:214)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:135)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:227)
at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:72)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:190)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:406)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:57)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:461)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:414)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:614)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:190)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:406)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:57)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:461)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:414)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:614)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:57)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:461)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:414)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:614)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:57)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:461)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:414)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:614)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:343)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:592)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:614)

[12/9/03 15:40:42:031 EST] 1936405f SystemErr R net.sf.hibernate.JDBCException: SQLException occurred
at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:145)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:215)
at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:314)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:117)
at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:306)
at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:43)
at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:41)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:941)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:662)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:214)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:135)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:227)
at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:72)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:190)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:406)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:57)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:461)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:414)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:614)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:227)
at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:72)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:190)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:406)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:614)
Caused by: java.sql.SQLException: ORA-00933: SQL command not properly ended
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:662)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:214)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:135)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:227)
at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:72)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:190)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:406)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:57)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:461)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:414)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:614)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:190)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:406)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:57)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:461)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:414)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:614)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:57)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:461)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:414)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:614)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:57)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:461)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:414)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:614)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:343)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:592)

should this not be "from FMUSER {usr}" ? AS is not allowed in a from clause (at least not on oracle ;)

You are perfectly right. I have corrected this error.

The real problem is my project is "Can I have a field which is a calculated field from another table"?
Say in my User mapping file which maps the class to FMUSER table, there is a field named "roleCount", it is a field which should be calculated by using aggregate function Count('FMROLE.USERID') to count the row numbers of userId appeared in second talbe FMROLE where FMUSER.USERID=FMROLE.USERID.

I tried adding/removing this field from mapping, both ways are not working. Actually, I am not sure if I can do so and/or how to do it.

Any advice?
Thanks
George