Trouble converting SQL query with subselect to Hibernate named query

Question

I have two tables and I want to select entries from table A who are referenced in table B using two fields present in both tables.

The two fields used to reference the entries are numeric, so the following query does what I want (but it is slow due to the multiplication and because table B is large).

select * from TIBP
where IBP_CODE * 100 + IBP_BASE in
      (select FZGLP_CODE * 100 + FZGLP_BASE from TFZGLP)

I've successfully mapped this to the following HQL named query:

<query name="findWhereReferencedInZglp">
    select tibp 
    from TibpEntity as tibp
    where tibp.code * 100 + tibp.base in
          (select zglp.code * 100 + zglp.base from ZglpEntity as zglp) 
</query>

To improve the speed of the above, I've modified the SQL statement as follows:

select * from TIBP as A,
              (select distinct FZGLP_CODE, FZGLP_BASE from TFZGLP) as B
where A.IBP_CODE = B.FZGLP_CODE and A.IBP_BASE = B.FZGLP_BASE

Sadly, I've not managed to turn this into a named HQL query that can be parsed. Is this even possible using HQL/Hibernate?

score 0 · Answer 1 · answered Oct 16 '13 at 15:09

0

Have you tried using an inner join; something like the following SQL:

select A.*,distinct B.FZGLP_CODE, B.FZGLP_BASE 
from TFZGLP B
inner join TIBP A on A.IBP_CODE = B.FZGLP_CODE
    where A.IBP_BASE = B.FZGLP_BASE

You can easily convert this to hibernate.

answered Oct 16 '13 at 15:09

Dan

217
2
11

My DB chokes on your statement because of the placement of "distinct". It works with the following, but obviously does not return the correct data: `select A.*, B.FZGLP_CODE, B.FZGLP_BASE from TFZGLP B inner join TIBP A on A.IBP_CODE = B.FZGLP_CODE where A.IBP_BASE = B.FZGLP_BASE` However, I've managed to turn your SQL into something that is both accepted by my DB and returns what I want: `select A.* from TIBP A join (select distinct FZGLP_CODE as code, FZGLP_BASE as base from TFZGLP) as B on A.IBP_CODE = B.CODE where A.IBP_BASE = B.BASE` – Urs Beeli Oct 17 '13 at 12:43
Sadly, that brings me no further because I still fail to "easily convert this to hibernate". My attempt so far: `select bp from BpEntity as bp join (select distinct zglp.base, zglp.code from ZglpEntity as zglp) on zglp.uicLaendercode = bp.uicLaendercode where zglp.bpUicCode = bp.bpCode` It still always fails on the opening bracket of the subselect (which had already been the problem in my original attempt): `ERROR org.hibernate.hql.internal.ast.ErrorCounter: line 5:9: unexpected token: (` – Urs Beeli Oct 17 '13 at 12:44
Can you use "group by" instead? Something like `select A.* from TFZGLP B inner join TIBP A on A.IBP_CODE = B.FZGLP_CODE where A.IBP_BASE = B.FZGLP_BASE group by B.FZGLP_CODE, B.FZGLP_BASE` You can find further details about the performance of `distinct` and `group by` [here](http://stackoverflow.com/questions/7943957/huge-performance-difference-when-using-group-by-vs-distinct) – Dan Oct 17 '13 at 12:47
Thanks for your response. I have been unable to make this work. However I try this, I always receive the following error: DB2 error code -122, see [link](http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.codes%2Fsrc%2Ftpc%2Fn122.htm) – Urs Beeli Oct 17 '13 at 14:36

Trouble converting SQL query with subselect to Hibernate named query

1 Answers1