We are trying to execute an oracle query which uses the lead function.
We are getting an exception which is pasted below. Can somebody say what we are missing ? Any help in this regard would be appreciated ! Thanks.


[b]Hibernate version:[/b] 2.1.7

[b]Mapping documents:[/b]

[b]Code between sessionFactory.openSession() and session.close():[/b]

<code>

List result = null;
Session session = getSession();
try {
String sql =
" select tran.dates, " +
" to_date(to_char(tran.transactionTs,'dd-mon-yyyy hh24:mi:ss'),'dd-mon-yyyy hh24:mi:ss') - "+
" lead(to_date(to_char(tran.transactionTs,'dd-mon-yyyy hh24:mi:ss'),'dd-mon-yyyy hh24:mi:ss')) " +
" over (order by tran.transactionTs desc) , " +
" to_char(tran.transactionTs,'HH:MI'), " +
" type.transactionType, " +
" loc.fcsNumber, " +
" card.pan, " +
" tran.panEntryMode," +
" tran.transactionAmount," +
" tran.feeAmount " +
" from FinancialTransaction tran, " +
" TransactionType type, " +
" LocationBrief loc, " +
" CardBrief card, " +
" CardBrief card1 " +
" where card.pan = :pan and " +
" card1.clientPrefix = card.clientPrefix and " +
" card1.clientKey = card.clientKey and " +
" tran.panPrefix = card.id.panPrefix and " +
" tran.cardKey = card.id.cardKey and" +
" tran.dates between :startDate and :endDate and " +
" loc.id.locationPrefix (+) = tran.locationPrefix and " +
" loc.id.locationKey (+) = tran.locationKey and " +
" type.id.transactionTypeKey = tran.transactionTypeKey"
;


Query q = session.createQuery(sql).setCacheable(true);
q.setMaxResults(10);
q.setFirstResult(10 * page);
q.setString("pan", pan);
q.setDate("startDate", startDate);
q.setDate("endDate", endDate);
result = q.list();
</code>
[b]Full stack trace of any exception that occurs:[/b]

org.springframework.orm.hibernate.HibernateQueryException: , expected in SELECT [ select tran.dates, to_date(to_char(tran.transactionTs,'dd-mon-yyyy hh24:mi:ss'),'dd-mon-yyyy hh24:mi:ss') - lead(to_date(to_char(tran.transactionTs,'dd-mon-yyyy hh24:mi:ss'),'dd-mon-yyyy hh24:mi:ss')) over (order by tran.transactionTs desc) , to_char(tran.transactionTs,'HH:MI'), type.transactionType, loc.fcsNumber, card.pan, tran.panEntryMode, tran.transactionAmount, tran.feeAmount from com.efunds.gov.gsdw.core.domain.FinancialTransaction tran, com.efunds.gov.gsdw.core.domain.TransactionType type, com.efunds.gov.gsdw.core.domain.LocationBrief loc, com.efunds.gov.gsdw.core.domain.CardBrief card, com.efunds.gov.gsdw.core.domain.CardBrief card1 where card.pan = :pan and card1.clientPrefix = card.clientPrefix and card1.clientKey = card.clientKey and tran.panPrefix = card.id.panPrefix and tran.cardKey = card.id.cardKey and tran.dates between :startDate and :endDate and loc.id.locationPrefix (+) = tran.locationPrefix and loc.id.locationKey (+) = tran.locationKey and type.id.transactionTypeKey = tran.transactionTypeKey]; nested exception is net.sf.hibernate.QueryException: , expected in SELECT [ select tran.dates, to_date(to_char(tran.transactionTs,'dd-mon-yyyy hh24:mi:ss'),'dd-mon-yyyy hh24:mi:ss') - lead(to_date(to_char(tran.transactionTs,'dd-mon-yyyy hh24:mi:ss'),'dd-mon-yyyy hh24:mi:ss')) over (order by tran.transactionTs desc) , to_char(tran.transactionTs,'HH:MI'), type.transactionType, loc.fcsNumber, card.pan, tran.panEntryMode, tran.transactionAmount, tran.feeAmount from com.efunds.gov.gsdw.core.domain.FinancialTransaction tran, com.efunds.gov.gsdw.core.domain.TransactionType type, com.efunds.gov.gsdw.core.domain.LocationBrief loc, com.efunds.gov.gsdw.core.domain.CardBrief card, com.efunds.gov.gsdw.core.domain.CardBrief card1 where card.pan = :pan and card1.clientPrefix = card.clientPrefix and card1.clientKey = card.clientKey and tran.panPrefix = card.id.panPrefix and tran.cardKey = card.id.cardKey and tran.dates between :startDate and :endDate and loc.id.locationPrefix (+) = tran.locationPrefix and loc.id.locationKey (+) = tran.locationKey and type.id.transactionTypeKey = tran.transactionTypeKey]
net.sf.hibernate.QueryException: , expected in SELECT [ select tran.dates, to_date(to_char(tran.transactionTs,'dd-mon-yyyy hh24:mi:ss'),'dd-mon-yyyy hh24:mi:ss') - lead(to_date(to_char(tran.transactionTs,'dd-mon-yyyy hh24:mi:ss'),'dd-mon-yyyy hh24:mi:ss')) over (order by tran.transactionTs desc) , to_char(tran.transactionTs,'HH:MI'), type.transactionType, loc.fcsNumber, card.pan, tran.panEntryMode, tran.transactionAmount, tran.feeAmount from com.efunds.gov.gsdw.core.domain.FinancialTransaction tran, com.efunds.gov.gsdw.core.domain.TransactionType type, com.efunds.gov.gsdw.core.domain.LocationBrief loc, com.efunds.gov.gsdw.core.domain.CardBrief card, com.efunds.gov.gsdw.core.domain.CardBrief card1 where card.pan = :pan and card1.clientPrefix = card.clientPrefix and card1.clientKey = card.clientKey and tran.panPrefix = card.id.panPrefix and tran.cardKey = card.id.cardKey and tran.dates between :startDate and :endDate and loc.id.locationPrefix (+) = tran.locationPrefix and loc.id.locationKey (+) = tran.locationKey and type.id.transactionTypeKey = tran.transactionTypeKey]
at net.sf.hibernate.hql.SelectParser.token(SelectParser.java:169)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
at net.sf.hibernate.hql.ClauseParser.end(ClauseParser.java:114)
at net.sf.hibernate.hql.PreprocessingParser.end(PreprocessingParser.java:143)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:295)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1571)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1542)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at com.efunds.gov.gsdw.core.dao.FinancialTransactionDAO.loadFinancialTransactions(FinancialTransactionDAO.java:109)
at com.efunds.gov.gsdw.core.dao.FinancialTransactionDAOTest.testLoadFinancialTransactions(FinancialTransactionDAOTest.java:40)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:41)
at java.lang.reflect.Method.invoke(Method.java:386)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:392)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:276)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:167)





[b]Name and version of the database you are using:[/b]
Oracle9i Enterprise Edition Release 9.2.0.6.0
[b]The generated SQL (show_sql=true):[/b]

[b]Debug level Hibernate log excerpt:[/b]

[quote="Yeshaswi"]Could anyone give a one line answer atleast ... if the question has not been put in the right format, let us know so that we could correct that. Thanks.[/quote]