Win a copy of JDBC Workbook this week in the JDBC and Relational Databases forum
or A Day in Code in the A Day in Code forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

Exception - Column ambigously defined in a native query

 
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
 
Montana has cold dark nights. Perfect for the heat from incandescent light. Tiny ad:
Devious Experiments for a Truly Passive Greenhouse!
https://www.kickstarter.com/projects/paulwheaton/greenhouse-1
    Bookmark Topic Watch Topic
  • New Topic