Hello folks, I am having bit of trouble with optimizing this hql. Appreciate if I could hear some second opinion from experts over here. Here is the problem:
Table:
InventoryitemId, itmType, transType, transTypeID, qty, transDate
To indicate inventory out the qty will be <0
BatchcodeInfobatchcode, transactions(Inventory), prodDate, expDate
this inventory table link to many table depends on its transType
for example:
DelvOrdid, transDate, delvAddr, customer
the id in this case is linked to transTypeID in Inventory table. Moreover, all this type transaction will have transType with value of 1
I am trying to create report which scan the same table more than once
solution i have right now is
Code:
select new Map(
itmID as iID,
itmType as iType,
batchcode as bCode,
sum(qty) as qtyOH,
(select sum(inv2.qty)
from batchcodeInfo b2
join batchcodeInfo.transactions as inv2
where inv2.transType='production'
and inv2.transDate<:maxDate
and inv2.transDate>:minDate
and inv2.itmID= inv.itmID
and inv2.itmType= inv.itmType
and b2.batchcode= b.batchcode
) as qtyProd,
(select sum(inv3.qty)
from batchcodeInfo b3
join batchcodeInfo.transactions as inv3
where inv3.transType= 2
and inv3.transDate<:initDate
and inv3.itmID= inv.itmID
and inv3.itmType= inv.itmType
and b3.batchcode= b.batchcode
) as qtyInit
... there are 6 other sub query like this with different criteria.
)
from batchcodeInfo b
join batchcodeInfo.transactions as inv
where inv.transDate<:maxDate
group by itmID, itmType, batchcode
I am having a hard time to avoid using query that scan the same table more than once. I want to avoid on solving this through java, but I might miss something and open for suggestion. Thanks in advance.
Best Regards
Jon
Login
FAQ
Search