Problem with column alias: Unknown column 'avg_rating' in 'where clause'

[Julien Martin]

Hello,
I have a basic sql statement as follows:

SELECT 
e.establishment_id,
e.establishment_name,
avg(rt.rating) avg_rating
FROM
 
establishment e,
rating rt,
comment com,
establishment_country ec,
country_ref cou 
 
where
 
etc...
 
and avg_rating >= 1
 
and 0=0
 
group by e.establishment_id
 
order by e.establishment_id
 

I have used a column alias for "avg(r.rating)" and named it "avg_rating".

It works in my Mysql Query browser without problem but when I run it from Java I get this:

java.sql.SQLException: Unknown column 'avg_rating' in 'where clause'
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:2988)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:2917)
    at com.mysql.jdbc.Statement.executeQuery(Statement.java:824)
    at org.jboss.resource.adapter.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:145)
    at arcoiris.SearchSessionBean.performSearch(SearchSessionBean.java:73)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:585)
    at org.jboss.invocation.Invocation.performCall(Invocation.java:345)
    at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:214)
    at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:149)
    at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:154)
    at org.jboss.webservice.server.ServiceEndpointInterceptor.invoke(ServiceEndpointInterceptor.java:54)
    at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:48)
    at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:106)
    at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:335)
    at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:166)
    at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:153)
    at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:192)
    at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)
    at org.jboss.ejb.SessionContainer.internalInvoke(SessionContainer.java:624)
    at org.jboss.ejb.Container.invoke(Container.java:873)
    at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:415)
    at org.jboss.ejb.plugins.local.StatelessSessionProxy.invoke(StatelessSessionProxy.java:88)
    at $Proxy172.performSearch(Unknown Source)
    at arcoiris.SearchManagedBean.performSearch(SearchManagedBean.java:171)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:585)
    at com.sun.faces.el.MethodBindingImpl.invoke(MethodBindingImpl.java:126)
    at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:72)
    at javax.faces.component.UICommand.broadcast(UICommand.java:312)
    at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:267)
    at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:381)
    at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:75)
    at com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:200)
    at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:90)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:197)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
    at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:81)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
    at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:39)
    at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:159)
    at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:59)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
    at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
    at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:112)
    at java.lang.Thread.run(Thread.java:595)

Can anyone help please?

Thanks in advance,

Julien Martin.

[Em Aiy]

use 'as' between avg(r.rating) avg.rating

[Julien Martin]

Hello Muhammad,
Thanks for the reply. However I still get the same exception after introducing the change: avg(r.rating) as avg_rating

10:22:23,078 INFO  [STDOUT] java.sql.SQLException: Unknown column 'avg_rating' in 'where clause'
10:22:23,078 INFO  [STDOUT]     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928)
10:22:23,078 INFO  [STDOUT]     at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
10:22:23,078 INFO  [STDOUT]     at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
10:22:23,078 INFO  [STDOUT]     at com.mysql.jdbc.Connection.execSQL(Connection.java:2988)
10:22:23,078 INFO  [STDOUT]     at com.mysql.jdbc.Connection.execSQL(Connection.java:2917)
10:22:23,078 INFO  [STDOUT]     at com.mysql.jdbc.Statement.executeQuery(Statement.java:824)
10:22:23,078 INFO  [STDOUT]     at org.jboss.resource.adapter.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:145)
10:22:23,078 INFO  [STDOUT]     at arcoiris.SearchSessionBean.performSearch(SearchSessionBean.java:73)
10:22:23,078 INFO  [STDOUT]     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
10:22:23,078 INFO  [STDOUT]     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
10:22:23,078 INFO  [STDOUT]     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
10:22:23,078 INFO  [STDOUT]     at java.lang.reflect.Method.invoke(Method.java:585)
10:22:23,078 INFO  [STDOUT]     at org.jboss.invocation.Invocation.performCall(Invocation.java:345)
10:22:23,078 INFO  [STDOUT]     at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:214)
10:22:23,078 INFO  [STDOUT]     at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:149)
10:22:23,078 INFO  [STDOUT]     at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:154)
10:22:23,078 INFO  [STDOUT]     at org.jboss.webservice.server.ServiceEndpointInterceptor.invoke(ServiceEndpointInterceptor.java:54)
10:22:23,078 INFO  [STDOUT]     at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:48)
10:22:23,078 INFO  [STDOUT]     at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:106)
10:22:23,078 INFO  [STDOUT]     at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:335)
10:22:23,078 INFO  [STDOUT]     at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:166)
10:22:23,078 INFO  [STDOUT]     at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:153)
10:22:23,078 INFO  [STDOUT]     at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:192)
10:22:23,078 INFO  [STDOUT]     at org.jboss.ejb.plugins.ProxyFactoryFinderInterceptor.invoke(ProxyFactoryFinderInterceptor.java:122)
10:22:23,078 INFO  [STDOUT]     at org.jboss.ejb.SessionContainer.internalInvoke(SessionContainer.java:624)
10:22:23,078 INFO  [STDOUT]     at org.jboss.ejb.Container.invoke(Container.java:873)
10:22:23,078 INFO  [STDOUT]     at org.jboss.ejb.plugins.local.BaseLocalProxyFactory.invoke(BaseLocalProxyFactory.java:415)
10:22:23,078 INFO  [STDOUT]     at org.jboss.ejb.plugins.local.StatelessSessionProxy.invoke(StatelessSessionProxy.java:88)
10:22:23,078 INFO  [STDOUT]     at $Proxy198.performSearch(Unknown Source)
10:22:23,078 INFO  [STDOUT]     at arcoiris.SearchManagedBean.performSearch(SearchManagedBean.java:171)
10:22:23,078 INFO  [STDOUT]     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
10:22:23,078 INFO  [STDOUT]     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
10:22:23,078 INFO  [STDOUT]     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
10:22:23,078 INFO  [STDOUT]     at java.lang.reflect.Method.invoke(Method.java:585)
10:22:23,078 INFO  [STDOUT]     at com.sun.faces.el.MethodBindingImpl.invoke(MethodBindingImpl.java:126)
10:22:23,078 INFO  [STDOUT]     at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:72)
10:22:23,078 INFO  [STDOUT]     at javax.faces.component.UICommand.broadcast(UICommand.java:312)
10:22:23,078 INFO  [STDOUT]     at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:267)
10:22:23,078 INFO  [STDOUT]     at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:381)
10:22:23,078 INFO  [STDOUT]     at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:75)
10:22:23,078 INFO  [STDOUT]     at com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:200)
10:22:23,093 INFO  [STDOUT]     at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:90)
10:22:23,093 INFO  [STDOUT]     at javax.faces.webapp.FacesServlet.service(FacesServlet.java:197)
10:22:23,093 INFO  [STDOUT]     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
10:22:23,093 INFO  [STDOUT]     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
10:22:23,093 INFO  [STDOUT]     at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:81)
10:22:23,093 INFO  [STDOUT]     at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
10:22:23,093 INFO  [STDOUT]     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
10:22:23,093 INFO  [STDOUT]     at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
10:22:23,093 INFO  [STDOUT]     at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
10:22:23,093 INFO  [STDOUT]     at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:39)
10:22:23,093 INFO  [STDOUT]     at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:159)
10:22:23,093 INFO  [STDOUT]     at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:59)
10:22:23,093 INFO  [STDOUT]     at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
10:22:23,093 INFO  [STDOUT]     at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
10:22:23,093 INFO  [STDOUT]     at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
10:22:23,093 INFO  [STDOUT]     at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
10:22:23,093 INFO  [STDOUT]     at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
10:22:23,093 INFO  [STDOUT]     at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744)
10:22:23,093 INFO  [STDOUT]     at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
10:22:23,093 INFO  [STDOUT]     at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:112)
10:22:23,093 INFO  [STDOUT]     at java.lang.Thread.run(Thread.java:595)
10:22:23,093 INFO  [CachedConnectionManager] Closing a connection for you.  Please close them yourself: org.jboss.resource.adapter.jdbc.WrappedConnection@3
a8f68   

any other idea?

Julien

[Em Aiy]

well, looking at the errors, the where clause is unable to recognize the column.

replace the avg_rating >= 1 to avg(rt.rating).

You can't use the alias in "where" clause, the where clause must be provided with the column names already defined in database.

I hope this will work.