[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-2211) A createQuery() caused ORA-00904 error

[Jitendra Singh (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-2211?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_27225 ] 

Jitendra Singh commented on HHH-2211:
-------------------------------------

Hi Sodia,

Your query is not generating correct sequel. It is generating : 

insert 
 into
  EVENTS_HISTORY
  ( EVENT_ID, EVENT_DATE, title ) select
   event0_.EVENT_ID as col_0_0_,
   event0_.EVENT_DATE as col_1_0_,
   event0_.title as col_2_0_ 
  from
   EVENTS event0_ 
  where
   (
    event0_.title like 'BUY%'
   ) 
   and (
    exists (
     select
      1 
     from
      PERSON_EVENT personeven1_ 
     where
      personeven1_.EVENT_ID=EVENTS.EVENT_ID
    )
   )

Look at the last line, it will not work as it is. If we modify the query to :
insert into EventHistory ( id, date, title ) 
	select id, date, title from Event e where e.title like 'BUY%'
	and exists
		( select 1 from PersonEvent p,Event ev where p.id=ev.id)
		
then it generates:
insert 
 into
  EVENTS_HISTORY
  ( EVENT_ID, EVENT_DATE, title ) select
   event0_.EVENT_ID as col_0_0_,
   event0_.EVENT_DATE as col_1_0_,
   event0_.title as col_2_0_ 
  from
   EVENTS event0_ 
  where
   (
    event0_.title like 'BUY%'
   ) 
   and (
    exists (
     select
      1 
     from
      PERSON_EVENT personeven1_,
      EVENTS event2_ 
     where
      personeven1_.EVENT_ID=event2_.EVENT_ID
    )
   )

which I think is the right query. 

Let me know if this is not what you are trying to do in the query.

Regards,
Jitendra

> A createQuery() caused ORA-00904 error
> --------------------------------------
>
>                 Key: HHH-2211
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2211
>             Project: Hibernate3
>          Issue Type: Bug
>          Components: query-hql
>    Affects Versions: 3.2.0.ga
>         Environment: JDK1.5.0+Eclipse3.2.1+Hibernate3.2.0.ga+Oracle9.2.0+WinXp(Prof)Sp2
>            Reporter: sodia
>            Priority: Trivial
>         Attachments: hiber-grammar-sql-and-errlog.zip, hiber-grammar-test.zip
>
>   Original Estimate: 2 weeks
>  Remaining Estimate: 2 weeks
>
> I want to try the following sql under hibernate,it failed.
> (The project is build on the basis of hibernate3.2's tutorial)
>         String hqlAlias = 
>         	"insert into EventHistory ( id, date, title )" +
>         	"  select id, date, title from Event e" +
>         	"    where e.title like 'BUY%'" +
>         	"      and exists" +
>         	"        ( select 1 from PersonEvent p" +
>         	"          where p.id=e.id)";
>                 int resultAlias = session.createQuery(hqlAlias).executeUpdate();
> But it works nice under sqlplus.
> INSERT INTO EVENTS_HISTORY
>   SELECT * FROM EVENTS
>     WHERE TITLE LIKE 'BUY%'
>       AND EXISTS
>         ( SELECT 1 FROM PERSON_EVENT
>           WHERE EVENT_ID=EVENTS.EVENT_ID);
> The 4 lines of error log is like this:
> 15:33:29,906 DEBUG SQL:393 - insert into EVENTS_HISTORY ( EVENT_ID, EVENT_DATE, title ) select event0_.EVENT_ID as col_0_0_, event0_.EVENT_DATE as col_1_0_, event0_.title as col_2_0_ from EVENTS event0_ where (event0_.title like 'BUY%') and (exists (select 1 from PERSON_EVENT personeven1_ where personeven1_.EVENT_ID=EVENTS.EVENT_ID))
> Hibernate: insert into EVENTS_HISTORY ( EVENT_ID, EVENT_DATE, title ) select event0_.EVENT_ID as col_0_0_, event0_.EVENT_DATE as col_1_0_, event0_.title as col_2_0_ from EVENTS event0_ where (event0_.title like 'BUY%') and (exists (select 1 from PERSON_EVENT personeven1_ where personeven1_.EVENT_ID=EVENTS.EVENT_ID))
> 15:33:29,984  WARN JDBCExceptionReporter:71 - SQL Error: 904, SQLState: 42000
> 15:33:29,984 ERROR JDBCExceptionReporter:72 - ORA-00904: "EVENTS"."EVENT_ID": 无效的？？符
> Is there any wrong with the hql,or it's alias?
> Thanks to anyone who pay attetion to this trivial question which I search the forum and google without solutions.
> Or if I missed them,I would be grateful that you might tell me where can I find the right answer to fix this hql grammar error.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira