SQL "column ambiguously defined"- when run from hibernate

Question

When I run a SQL query from sqldeveloper , it runs fine When I run the same query from Hibernate SQL session, it gives me: "ORA-00918: column ambiguously defined" error.
Dies hibernate generate any kind of sql logs that can be used to debug the error?

Edit: here is the sql:(works fine from sqldeveloper)

    SELECT main_contact.cont_name,
  sub_contact.cont_name,
  main_contact.cont_role_desc,
  main_contact.cont_start_dte,
  main_contact.cont_end_dte,
  main_contact.cont_id,
  sub_contact.cont_id,
  main_contact.lookup_desc,
  main_contact.cont_role_desc 
FROM 
  (SELECT cont_rlat.cont_rlat_id ,
    cont_role.cont_role_desc ,
    cont.cont_name ,
    cont.cont_ty_cde ,
    cont.cont_sid ,
    cont.cont_id ,
    cont_rlat.rlat_from_dte ,
    cont.cont_start_dte ,
    cont.cont_end_dte ,
    cont_rlat.app_id ,
    lookup_data_mgmt.lookup_desc
  FROM cont_rlat join
    cont on cont_rlat.cont_sid      = cont.cont_sid
     join cont_role on cont_rlat.cont_role_id=cont_role.cont_role_id
   join  app on cont_rlat.app_id     = app.app_ID
     join LOOKUP_DATA_MGMT on app.app_TY_CDE    = LOOKUP_DATA_MGMT.LOOKUP_ID
   where 
 app.app_id        =:investmentProfileCId
  ) main_contact left join 
  (SELECT cont.cont_id,
    cont.cont_name,
    cont_sub_rlat.cont_rlat_id
  FROM cont_sub_rlat join 
    cont on   
   cont_sub_rlat.individual_cont_id = cont.cont_sid
  ) sub_contact on 

 main_contact.cont_rlat_id = sub_contact.cont_rlat_id

the column might be buried in the stack trace, please post. as well as the mapping for the table. — hvgotcodes, Jan 27 '11 at 00:37
Thanks..please see edit. Also..the column is not in stack trace.I searched there. — Victor, Jan 27 '11 at 16:52

Matt Gibson · Accepted Answer · 2011-01-27T17:00:55.987

You're selecting main_contact.cont_role_desc twice. Also, you have two columns called 'cont_name' and two columns called 'cont_id', which also might not make it happy. If dropping the extra cont_name doesn't work, maybe try aliasing those?

SELECT main_contact.cont_name,
  sub_contact.cont_name AS sub_cont_name,
  main_contact.cont_role_desc,
  main_contact.cont_start_dte,
  main_contact.cont_end_dte,
  main_contact.cont_id,
  sub_contact.cont_id AS sub_cont_id,
  main_contact.lookup_desc

Neither of those will be a problem in SQLDeveloper, but I imagine that at the very least having two exact copies of main_contact.cont_role_desc in the result set will be confusing Hibernate -- how will it tell them apart?

score 0 · Answer 2 · answered Jan 27 '11 at 00:37

0

in hibernate.cfg.xml to make it log sql statements

<property name="hibernate.show_sql">true</property>

answered Jan 27 '11 at 00:37

fmucar

14,361
2
45
50

I know the sql..it is running from an sqldeveloper window perfectly. when i run the same sql in hibernate, there is thsi error – Victor Jan 27 '11 at 05:00

SQL "column ambiguously defined"- when run from hibernate

2 Answers2