Grammar Exception with Oracle Dialect using left outer join

mado · **Joined:** Thu Feb 23, 2006 6:33 pm **Posts:** 2

Hi,

With this HQL:

Code:

from Company comp 
left outer join comp.employees empl 
[b]with empl.birthday > :date [/b]

Produce following SQL:

Code:

    select 
        company0_.id as id0_0_, 
        employees1_.id as id1_1_, 
        company0_.companyName as companyN2_0_0_, 
        employees1_.name as name1_1_, 
        employees1_.birthday as birthday1_1_, 
        employees1_.companyId as companyId1_1_ 
    from 
        TB_COMPANY_TEMP company0_, 
        TB_EMPLOYEE_TEMP employees1_ 
    where 
        company0_.id=employees1_.companyId(+) 
        and [b](employees1_.birthday(+)>(+)=?) [/b]

When it is run, the stacktrace is:

Code:

org.hibernate.exception.SQLGrammarException: could not execute query
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.loader.Loader.doList(Loader.java:2148)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
   at org.hibernate.loader.Loader.list(Loader.java:2024)
   at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
   at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:308)
   at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:153)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1129)
   at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
   at mytest.ant.OuterJoinTest.main(OuterJoinTest.java:28)
Caused by: java.sql.SQLException: ORA-00936: missing expression

   at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
   at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
   at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
   at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
   at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:643)
   at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:1674)
   at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1870)
   at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:363)
   at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:314)
   at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
   at org.hibernate.loader.Loader.doQuery(Loader.java:662)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
   at org.hibernate.loader.Loader.doList(Loader.java:2145)
   ... 8 more

The correct where clause should be :

Code:

    where 
        company0_.id=employees1_.companyId(+) 
        and (employees1_.birthday(+)>=?)

This error don't occur with MySQLDialect or SQLServerDialect. Probably it considers >= as two operators instead of a single operator.

Anyone had same problemn?

Marcelo Oliveira

jt_1000 · **Posted:** Fri Feb 24, 2006 12:39 pm

did you try using SQLQuery?

eg:

Code:

SQLQuery q=sess.createSQLQuery("select {comp.*} " + 
" from Company comp " +  
" left outer join comp.employees empl "   + 
" with empl.birthday > :date");

sess.addEntity("comp",Company.class);

mado · **Joined:** Thu Feb 23, 2006 6:33 pm **Posts:** 2

Hi jt,

jt_1000 wrote:

did you try using SQLQuery?

eg:

Code:

SQLQuery q=sess.createSQLQuery("select {comp.*} " + 
" from Company comp " +  
" left outer join comp.employees empl "   + 
" with empl.birthday > :date");

sess.addEntity("comp",Company.class);

I tried and got this error:

Code:

    select
        comp.id as id0_0_,
        comp.companyName as companyN2_0_0_  
    from
        Company comp  
    left outer join
        comp.employees empl  with empl.birthday > ?
org.hibernate.exception.SQLGrammarException: could not execute query
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.loader.Loader.doList(Loader.java:2148)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
   at org.hibernate.loader.Loader.list(Loader.java:2024)
   at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:111)
   at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1674)
   at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:147)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:164)
   at mytest.ant.OuterJoinTest.main(OuterJoinTest.java:36)
Caused by: java.sql.SQLException: ORA-00933: SQL command not properly ended

   at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:168)
   at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
   at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
   at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
   at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:643)
   at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:1674)
   at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1870)
   at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:363)
   at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:314)
   at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
   at org.hibernate.loader.Loader.doQuery(Loader.java:662)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
   at org.hibernate.loader.Loader.doList(Loader.java:2145)
   ... 7 more
060303@19:54:56 ERROR (JDBCExceptionReporter.java:72)     - ORA-00933: SQL command not properly ended

jt_1000 · **Posted:** Fri Mar 03, 2006 8:54 pm

what I meant was to convert it to a native SQL Query.

Here is a working example:

SQL code to create table and insert sample records:

Code:

create table company(coid number primary key, name varchar2(32));

create table person(pid number primary key, fkcoid number, 
pname varchar2(32),
birthdate date);

alter table person add constraint fk_co foreign key (fkcoid) references
company(coid);

delete from person;
delete from company;

insert into company values(1,'acme');
insert into company values(2,'toy-r-us');
insert into company values(3,'walmart');
insert into company values(4,'no people');
insert into person values(1,1,'jon1',sysdate-50);
insert into person values(2,1,'bob1',sysdate-3);
insert into person values(3,1,'sue1',sysdate-1);
insert into person values(4,2,'marty2',sysdate-5);
insert into person values(5,2,'fred2',sysdate-2);
insert into person values(6,3,'jeff3',sysdate);
commit;

SQL code to do outter join select.

Code:

select 
        comp.coid , 
        comp.name  ,
        p.pname
    from 
        Company comp  
    left outer join 
        person p  on p.fkcoid=comp.coid
        and p.birthdate > sysdate-5;