Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

HQL : trimming in the on-clause with left outer join

 
Albert Kam
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all ...

I've been using left outer joins in HQL successfully so far, by defining the association, and everything runs smoothly, until i face this case :
The columns used on the on-clause have different types, one is VARCHAR, the other is CHAR

The partially generated SQL from my HQL is :
left outer join AM20 am20x5_ on mstmateria0_.PROD_CODE=am20x5_.BRGCOD

In SQL, it's easy to solve just by specifying the trim for the joined column :
left outer join AM20 am20x5_ on trim(mstmateria0_.PROD_CODE)=trim(am20x5_.BRGCOD)

I've tried the with-clause (in desperation and hope), but the resulted SQL is :
left outer join AM20 am20x5_ on mstmateria0_.PROD_CODE=am20x5_.BRGCOD and trim(am20x5_.BRGCOD)=trim(mstmateria0_.PROD_CODE))

I've searched also for possible attribute like autotrim="true" in column and also in set or the many-to-one elements, but i've found nothing related.

Is there any way i can do this without resorting to native sql ?

Thank you !

 
Freddy Wong
Ranch Hand
Posts: 959
Eclipse IDE Java Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As far as I know, you can't have do that in HQL. The only way is to resort to native SQL.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic