• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

MySql query redesign to JPA query

 
rajasekhar kannamaneni
Greenhorn
Posts: 27
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

can any one please help me how to redesign this mysql query into jpql(jpa query).

SELECT delivered.ls_fk_artikel, delivered.SUM_Amount_Delivered, sold.SUM_Amount_Sold, (coalesce(delivered.SUM_Amount_Delivered,0) - coalesce(sold.SUM_Amount_Sold,0)) AS Amount_Stock
FROM
(
SELECT
Sum(ls_main.ls_menge) AS SUM_Amount_Delivered,
ls_main.ls_fk_artikel,
ls_main.ls_ean,
ls_main.ls_artikelnr
FROM
ls_head
Inner Join ls_main ON ls_head.ls_head_id = ls_main.fk_ls_head
WHERE
ls_head.fk_filialen = '2410' AND
ls_head.storno IS NULL
GROUP BY
ls_main.ls_fk_artikel
) AS delivered
left JOIN
(
SELECT
coalesce(Sum(sales.anzahl), 0) AS SUM_Amount_Sold,
sales.fk_artikel,
sales.ean,
sales.artikel_nr
FROM
sales
WHERE
sales.fk_filialen = '2410'
GROUP BY
sales.fk_artikel
) AS sold ON delivered.ls_fk_artikel = sold.fk_artikel;

lsmain is one table, where I have to get delivered information, sales is sold data and I have to caliculate the difference between the delivered and sold stock.

Currently I am using the below query

Query query = this.entityManager.createQuery("select l, sum(l.ls_menge), s, sum(s.anzahl) from LsMainImpl l, SalesImpl s inner join l.fk_ls_head h where h.fk_filialen.filial_id = " + filial_id + " and h.storno is null and l.ls_fk_artikel.artikel_id = s.fk_artikel.artikel_id and s.fk_filialen.filial_id = " + filial_id + " group by l.ls_fk_artikel.artikel_id");

but it taking 250 seconds..where as the above query takes only 3 seconds.

can any one please help me how to do that.

Thanks in Advance,


Best Regards,
Raja.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic