I have the following query, When I tried to execute it using hibernate I get the following exception :
org.hibernate.exception.SQLGrammarException: could not execute query using scroll.
In my code I have scroll has FORWARD_ONLY. Can anyone please provide a perfect solution.
select * from (SELECT account_no AS accountno,rownum r FROM sc_dcm_postpaid_index WHERE groupid = 'SBG-2012'
and concat(trim(TO_CHAR(bill_date,'MONTH')),concat('-',TO_CHAR(bill_date,'YY')))='JUNE-12'
AND ROWID IN (SELECT MAX(ROWID) AS row_no ; FROM sc_dcm_postpaid_index
WHERE groupid= 'SBG-2012' and concat(trim(TO_CHAR(bill_date,'MONTH')),concat('-',TO_CHAR(bill_date,'YY')))='JUNE-12'
GROUP BY account_no HAVING COUNT (account_no) >= 1 ) ORDER BY account_no)where r >= 11 and r <= 21..
Also when I change the query to
SELECT account_no AS accountno,rownum r FROM sc_dcm_postpaid_index WHERE rownum >= 11 and rownum <= 21 groupid = 'SBG-2012'
and concat(trim(TO_CHAR(bill_date,'MONTH')),concat('-',TO_CHAR(bill_date,'YY')))='JUNE-12'
AND ROWID IN (SELECT MAX(ROWID) AS row_no ; FROM sc_dcm_postpaid_index
WHERE groupid= 'SBG-2012' and concat(trim(TO_CHAR(bill_date,'MONTH')),concat('-',TO_CHAR(bill_date,'YY')))='JUNE-12'
GROUP BY account_no HAVING COUNT (account_no) >= 1 ) ORDER BY account_no
And this is my Query:
SQLQuery mainQuery = (SQLQuery) session.createSQLQuery(strReportQuery);
ScrollableResults itrDataList = mainQuery.scroll(ScrollMode.FORWARD_ONLY);
Here strReportQuery is my query
It executes fine but it works only from rownum 1 to 10. From next rownum it gives resultset as empty.
Thanks in Advance, Bhargavi T.
