org.hibernate.exception.SQLGrammarException: could not execute query

[pankaj semwal]

Hi list[],
I am getting following exception :

Hibernate: select this_.BRCD_DLR_NO as y0_, this_.BRCD_PART_NO as y1_, rewclm1_.WCLP_CLM_NO as y2_ from REBRCD_TB this_ inner join REWCLP_TB rewclm1_ on this_.BRCD_CMPN_NO=rewclm1_.WCLP_CMPN_NO and this_.BRCD_CORP_NO=rewclm1_.WCLP_CORP_NO and this_.BRCD_DLR_NO=rewclm1_.WCLP_DLR_NO and this_.BRCD_WCLM_NO=rewclm1_.WCLP_CLM_NO and this_.BRCD_PART_NO=rewclm1_.WCLP_PART_NO where  TO_CHAR(BRCD_CRTE_TIME,'YYYYMMDD')BETWEEN '20110728' AND '20110729' and y1_=? order by this_.BRCD_CRTE_TIME  asc
org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.loader.Loader.doList(Loader.java:2231)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
	at org.hibernate.loader.Loader.list(Loader.java:2120)
	at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:118)
	at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1596)
	at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306)
	at hmil.com.barcode.dao.TechnicalEntryDAO.showDetails(TechnicalEntryDAO.java:44)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.lang.reflect.Method.invoke(Unknown Source)

.
..
..
..
..
..
Caused by: java.sql.SQLException: ORA-00904: "Y1_": invalid identifier

	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
	at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
	at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
	at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
	at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:850)
	at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2599)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2963)
	at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:658)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:584)
	at net.sf.log4jdbc.PreparedStatementSpy.executeQuery(PreparedStatementSpy.java:593)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
	at org.hibernate.loader.Loader.doQuery(Loader.java:697)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
	at org.hibernate.loader.Loader.doList(Loader.java:2228)
	... 80 more
could not execute query

And i am calling like this

Criteria criteria = getSession().createCriteria(BarCodeEntry.class);
	   criteria.setFetchMode("rewclp", FetchMode.JOIN);
	   criteria.createAlias("rewclp", "reWclm");	  
	   criteria.add(Restrictions.sqlRestriction(" TO_CHAR(BRCD_CRTE_TIME,'YYYYMMDD')BETWEEN '" +fromDate+ "' AND '"+ toDate+"'" ));
	  ProjectionList projections = Projections.projectionList();
	  projections.add(Projections.property("BRCD_DLR_NO"), "BRCD_DLR_NO");
	    projections.add(Projections.property("BRCD_PART_NO"),"BRCD_PART_NO");
	    projections.add(Projections.property("reWclm.WCLP_CLM_NO"),"WCLP_CLM_NO");
	  if(partNo!=null && partNo.length()>0)
	  {
		criteria.add(Restrictions.eq("BRCD_PART_NO",partNo));
		  
	  } 
        criteria.addOrder(Order.asc("BRCD_CRTE_TIME"));
        criteria.setProjection(projections);
        criteria.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<BarCodeEntry>  lst=criteria.list();
      
     
       
	  	  
	  return lst; 

Pl tell me where i am wrong.

[Waswani Naresh]

criteria.add(Restrictions.sqlRestriction(" TO_CHAR(BRCD_CRTE_TIME,'YYYYMMDD')BETWEEN '" +fromDate+ "' AND '"+ toDate+"'" ));

There is no space before 'BETWEEN'. Try giving a space before it.

Regards,
Naresh Waswani

[joy b chakravarty]

From what I understand the problem doesn't lie in the Between cluase

If you look at the generated query you have something like this
select this_.BRCD_DLR_NO as y0_, this_.BRCD_PART_NO as y1_,
...
where TO_CHAR(BRCD_CRTE_TIME,'YYYYMMDD')BETWEEN '20110728' AND '20110729' and y1_=?
This should have been this_.BRCD_PART_NO = ?

i think use of Projections is causing this to happen..

criteria.add(Restrictions.eq("BRCD_PART_NO",partNo));  
//try using this instead
criteria.add( Restrictions.sqlRestriction("{alias}.BRCD_PART_NO =?", partNo, Hibernate.STRING) );

[pankaj semwal]

Thanks for reply,
I solve the problem as i use alias name like this.

 Criteria criteria = getSession().createCriteria(BarCodeEntry.class,"bar");