hi

yesterday i posted a message to know whether sub query can be written inside aggregate function.the answer was no.

Now i am trying to write the following query with createSQLQuery(). but it is giving a invalid character exception. the equivalent of the following query is running in Oracle console.

please tell me whether the query syntax is correct or not. and how can i made this query executed.

String[] alias = {"emp","att"};
Class[] cl = {AttendanceLogonDto.class,EmployeeDto.class};

String stringQuery = "select {emp}.emp_code,count(decode({att}.logon_status,'P','Present')) " +
"Present,count(decode({att}.logon_status,'L','Late')) Late," +
"count(decode({att}.logon_status,'A','Absent')) Absent," +
"count(decode({att}.logon_status,'V','Leave')) Leave " +
"from emp_info {emp},attend_logon_status {att} where {emp}.pk_code={att}.emp_f_code and" +
" {att}.logon_date between '01-May-2004'and '20-May-2004'group by {emp}.emp_code;";


Query query = session.createSQLQuery(stringQuery,alias,cl);
//query.setDate("param1", dto.getPeriodFromType());
//query.setDate("param2", dto.getPeriodToType());

return query.list();

The following exception is generated-------------------------


java.sql.SQLException: ORA-00911: invalid character

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1451)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:651)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2117)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2331)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:422)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:366)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:132)
at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
at net.sf.hibernate.loader.Loader.list(Loader.java:940)
at net.sf.hibernate.loader.SQLLoader.list(SQLLoader.java:92)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3628)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
at com.diit.voffice.attendance.report.ReportDao.searchAllEmployeePL(ReportDao.java:300)
at com.diit.voffice.attendance.report.ReportViewHelper.handleEvent(ReportViewHelper.java:514)
at com.diit.voffice.event.EventProcessor.processMultithreaded(EventProcessor.java:109)
at com.diit.voffice.event.EventProcessor.processEvent(EventProcessor.java:76)
at com.diit.voffice.attendance.TestReport.testSearch(TestReport.java:74)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
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:395)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:279)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:171)
2004-05-12 12:13:11,062 DEBUG [main] util.JDBCExceptionReporter (JDBCExceptionReporter.java:36) - SQL Exception

Don't use a ; at the end of your query!