Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Exception - Column ambigously defined in a native query

 
hildich kilal
Ranch Hand
Posts: 44
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello
I am having a native query which joins two entities and returns instances of both of them using SQLResultSetMapping. Now I need to have pagination on it. For that I am using setFirstResult() and setMaxResult() over it.

Now problem is that both the tables have few columns which have exactly same name. So in entities also i have named the attributes matching column names . For example id , sourceType , sourceCode. The query returned is something like
select * from ( select row_.*, rownum rownum_ from ( Select e1.id, e1.sourceCode, e1.sourceType, ..... , e2.id, e2.sourceCode, e2.sourceType ..... from EntityClass1 e1 left outer join EntityClass e2 on ... ) row_ where rownum <= ?) where rownum_ > ?

Now when this query is executed I get the exception "Caused by: java.sql.SQLException: ORA-00918: column ambiguously defined".
Can any one help me how I can resolve this? or the only solution will be to name the attributes differently in the two entity classes?

Thanks

 
hildich kilal
Ranch Hand
Posts: 44
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I was finally able to solve this problem. All i did was follow the rules for creating native query.
There are couple of things I did. As my entity classes were a combination of all possible (complex) cases which can be there in native query it took my almost a day to figure out solution. It had composite embedded id keys, same name of columns , two entities returned in one query etc etc.

I did following
1) Used SQLResultSetMapping for having more than one entity from query.
2) Used alias for duplicate column names in one of the entities.
3) Defined the Composite key mapping in SQLResultSetMapping for one of the entities.

Thanks
himanshu Kapil
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic