I'm currently in a project where we store orders of clients.
To give an overview to a sales representative we use the following hsql query to show the intakes of which the last state has a code which contains a value of either :code1 or :code2
Each intake is part of an order which has the purchase date:
Code:
sHql = "from Intake intake where intake.product.department=:department ";
sHql += " and ";
sHql += " ( ";
sHql += " intake.states[ maxindex(intake.states)].type.code like :code1 or ";
sHql += " intake.states[ maxindex(intake.states)].type.code like :code2 ";
sHql += " ) ";
sHql += " order by intake.order.dateOfPlacement desc";
Now the problem arrises that when we execute this query we get multiple versions of the same intake, for example we gat 25 listings of one intake where I expect just 1 listing for each intake. If I remove the second (:code2) check the query runs fine.
Hibernate generates the following sql for Oracle:
Code:
select intake0_.id as id, intake0_.xml as xml, intake0_.intake_hash as intake_h3_, intake0_.prijs as prijs, intake0_.backoffice_id as backoffi5_, intake0_.order_id as order_id, intake0_.product_id as product_id
from ew_tintake intake0_, ew_vproduct product1_, ew_tintakestatus states2_, ew_tintakestatustype intakestat4_, ew_tintakestatus states5_, ew_tintakestatustype intakestat7_, ew_torder order8_
where
intake0_.order_id=order8_.id and
((product1_.afdeling_id=405 and intake0_.product_id=product1_.instantieid) and
((intakestat4_.code like '%ERROR%' and states2_.type_id=intakestat4_.id and intake0_.id=states2_.intake_id and states2_.volg_nr = (select max(volg_nr) from ew_tintakestatus states3_ where intake0_.id=states3_.intake_id)) or
(intakestat7_.code like '%AUTHORISED%' and states5_.type_id=intakestat7_.id and intake0_.id=states5_.intake_id and states5_.volg_nr = (select max(volg_nr) from ew_tintakestatus states6_ where intake0_.id=states6_.intake_id))))
order by order8_.datum_order desc
Can anyone help me with changing this query to one which functions properly?
Login
FAQ
Search