0

I have a query like the one shown below:

SELECT
  SUM (LIMIT_AMOUNT)
FROM
  (SELECT
    CTR_NO
    , REPORT_DATE
    , LIMIT_AMOUNT
    , ROW_NUMBER()
  OVER (PARTITION BY CTR_NO, REPORT_DATE ORDER BY REPORT_DATE) rn
  FROM LOD_CONTRACT
  WHERE
    br_cst_code='3432434'
  AND REPORT_DATE BETWEEN '20-FEB-15' AND '28-FEB-15') b
WHERE
  b.rn=1;

How can I build a SQL query for a JPA-managed entity like:

SELECT
  SUM (o.limit_amount)
FROM
  (SELECT
    o.ctr_no
    , o.rpt_dt
    , o.limit_amount
    , ROW_NUMBER()
  OVER (PARTITION BY o.ctr_no, o.rpt_dt ORDER BY o.rpt_dt) rn
  FROM LOD_CONTRACT o
  WHERE
    o.br_cst_code='3432434'
  AND o.rpt_dt BETWEEN '20-FEB-15' AND '28-FEB-15') b
WHERE
  b.rn=1;
Improve this question
2
  • Your query is correct. What is the problem you are facing? Commented Aug 10, 2015 at 3:39
  • do you mean "how can i create a JPQL query equivalent to this SQL query?" If that is the case, then you can't ... you can't have a SUBQUERY in the select clause, you can't have OVER keyword. That should be evident from looking at any half decent JPQL docs Commented Aug 10, 2015 at 6:25

1 Answer 1

Reset to default
1

JPQL doesn't currently support Oracle partition tables. To use this feature you will have to stick to Native queries.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.