Please note that I use inverted CONVERT(TYPE,VALUE) described on Issue 274<http://code.google.com/p/h2database/issues/detail?id=274>
On Wednesday, June 13, 2012 12:18:06 PM UTC+2, joseaio wrote: > > > H2 throws "Unknown data type: 'T1'" Error on my query (see at end), > > This query works fine on Sybase database > > I can't reduce this query to simple case of error... sorry > > Any idea? (I think that alias 'T1' lost on nested > from-select-from-select...) > > es.rbcdexia.ulysses.util.DataAccessException: store.data.find.query (An > error happened trying to execute the finder: > StatementLine.findEntryLinesOrderAsc) > at > es.rbcdexia.ulysses.thirdparty.HibernateStore$HibernateIterator.init(HibernateStore.java:1302) > at > es.rbcdexia.ulysses.thirdparty.HibernateStore$HibernateIterator.hasNext(HibernateStore.java:1327) > at > es.rbcdexia.ulysses.account.report.StatementEntryLineIterator.initFirst(StatementEntryLineIterator.java:55) > at > es.rbcdexia.ulysses.account.report.StatementEntryLineIterator.<init>(StatementEntryLineIterator.java:41) > at > es.rbcdexia.ulysses.account.report.StatementLineService.internalFindEntryLines(StatementLineService.java:290) > at > es.rbcdexia.ulysses.account.report.StatementLineService.iterateStatementLines(StatementLineService.java:149) > at > es.rbcdexia.ulysses.thirdparty.FilterInterceptor.invoke(FilterInterceptor.java:37) > at > es.rbcdexia.ulysses.account.report.StatementLineServiceTest.testFindAscending(StatementLineServiceTest.java:74) > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:601) > at junit.framework.TestCase.runTest(TestCase.java:168) > at junit.framework.TestCase.runBare(TestCase.java:134) > at junit.framework.TestResult$1.protect(TestResult.java:110) > at junit.framework.TestResult.runProtected(TestResult.java:128) > at junit.framework.TestResult.run(TestResult.java:113) > at junit.framework.TestCase.run(TestCase.java:124) > at junit.framework.TestSuite.runTest(TestSuite.java:243) > at junit.framework.TestSuite.run(TestSuite.java:238) > at > org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:83) > at > org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50) > at > org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) > at > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467) > at > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683) > at > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390) > at > org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197) > Caused by: org.hibernate.exception.GenericJDBCException: could not execute > query using scroll > at > org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140) > at > org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128) > at > org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) > at org.hibernate.loader.Loader.scroll(Loader.java:2557) > at > org.hibernate.loader.custom.CustomLoader.scroll(CustomLoader.java:322) > at > org.hibernate.impl.SessionImpl.scrollCustomQuery(SessionImpl.java:1808) > at > org.hibernate.impl.AbstractSessionImpl.scroll(AbstractSessionImpl.java:170) > at org.hibernate.impl.SQLQueryImpl.scroll(SQLQueryImpl.java:205) > at > es.rbcdexia.ulysses.thirdparty.HibernateStore$HibernateIterator.init(HibernateStore.java:1295) > ... 26 more > Caused by: org.h2.jdbc.JdbcSQLException: Unknown data type: "T1"; SQL > statement: > SELECT R1.*, > R2.CLASS AS R2_CLASS, > R2.AUDIT_USER_NAME AS R2_AUDIT_USER_NAME, > R2.AUDIT_TIMESTAMP AS R2_AUDIT_TIMESTAMP, > R2.AUDIT_PROCESS_DATE AS R2_AUDIT_PROCESS_DATE, > R3.TEXT AS R3_TEXT, > R3.LETTER AS R3_LETTER, > R3.CODE_AEB43 AS R3_CODE_AEB43, > R3.STATUS AS R3_STATUS > FROM > ( > SELECT E.REFERENCE, E.BALANCE_ID, E.CURRENCY, E.IBAN, > E.ENTITY_CODE, E.BRANCH, > E.PROCESS_DATE, E.VALUE_DATE, E.GROUP_REFERENCE, > MIN(E.INTERNAL_REFERENCE) AS INTERNAL_REFERENCE, MIN(E.ISIN) AS ISIN, > MIN(E.EXTERNAL_REFERENCE) AS EXTERNAL_REFERENCE, MIN(E.CEC) AS CEC, > E.APPLICATION, > E.SIMPLE_PROCESS_DATE, E.TEXT, E.BANK_ORDER_ID, > E.CANCELLED_ENTRY_ID, > E.AUDITABLE_ID, E.CANCELLED_ID, > MIN(E.ID) AS ID, MIN(E.ENTRY_CONCEPT_ID) AS > ENTRY_CONCEPT_ID, > MIN(E.DOCUMENT_NUMBER) AS DOCUMENT_NUMBER, > SUM(E.AMOUNT) AS AMOUNT > FROM > ( > SELECT > T1.ID, > (CASE > WHEN T1.GROUP_REFERENCE IS NULL THEN 'I' + > CONVERT(VARCHAR,T1.ID) > ELSE 'R' + CONVERT(VARCHAR,T1.GROUP_REFERENCE) > END) AS REFERENCE, > T1.BALANCE_ID, > T2.CURRENCY, T3.IBAN, T3.ENTITY_CODE, T6.BRANCH, > T1.PROCESS_DATE, > T1.VALUE_DATE, > T1.SIMPLE_PROCESS_DATE, > dbo.grouped_text(T1.GROUP_REFERENCE, > T1.ENTRY_CONCEPT_ID, T1.TEXT) AS TEXT, > T1.ENTRY_CONCEPT_ID, > (CASE > WHEN T1.GROUP_REFERENCE IS NULL THEN > T1.BANK_ORDER_ID > ELSE NULL > END) AS BANK_ORDER_ID, > (CASE WHEN T1.GROUP_REFERENCE IS NULL > THEN T1.CANCELLED_ENTRY_ID > ELSE NULL > END) AS CANCELLED_ENTRY_ID, > (CASE > WHEN T1.GROUP_REFERENCE IS NULL THEN > T6.AUDITABLE_ID > ELSE NULL > END) AS AUDITABLE_ID, > (CASE > WHEN T1.GROUP_REFERENCE IS NULL THEN T4.ID > ELSE NULL > END) AS CANCELLED_ID, > T1.DOCUMENT_NUMBER, > T1.AMOUNT, > T1.GROUP_REFERENCE, > T1.INTERNAL_REFERENCE, > T1.ISIN, > T1.EXTERNAL_REFERENCE, > T1.CEC, > T7.APPLICATION > FROM ENTRY AS T1 > INNER JOIN BALANCE T2 ON T1.BALANCE_ID = T2.ID > INNER JOIN ACCOUNT T3 ON T2.ACCOUNT_ID = T3.ID > LEFT OUTER JOIN ENTRY T4 ON T1.ID = > T4.CANCELLED_ENTRY_ID > LEFT OUTER JOIN ENTRY T5 ON T1.CANCELLED_ENTRY_ID > = T5.ID > LEFT OUTER JOIN BANK_ORDER T6 ON T1.BANK_ORDER_ID > = T6.ID > LEFT OUTER JOIN AUDITABLE T7 ON T7.ID = > T6.AUDITABLE_ID > WHERE > ( > (T1.CANCELLED_ENTRY_ID = NULL) OR > (T1.SIMPLE_PROCESS_DATE > T5.SIMPLE_PROCESS_DATE) > ) > AND > ( > (T4.ID = NULL) OR > (T4.SIMPLE_PROCESS_DATE > > T1.SIMPLE_PROCESS_DATE) > ) > AND T3.IBAN = ? > AND T2.CURRENCY = ? > AND T1.PROCESS_DATE>= ? > AND T1.PROCESS_DATE<= ? > > > AND 'AUTHORIZATOR' not in ( > select roles.rol > from ROLE_FLAG roles > inner join ACCOUNT_FLAG flags on flags.FLAG = > roles.FLAG > inner join ACCOUNT accounts on accounts.ID = > flags.ACCOUNT_ID > where accounts.IBAN=T3.IBAN > ) > > > ) E > GROUP BY > E.REFERENCE, E.BALANCE_ID, > E.CURRENCY, E.IBAN, E.ENTITY_CODE, E.BRANCH, > E.PROCESS_DATE, E.VALUE_DATE, E.GROUP_REFERENCE, > E.SIMPLE_PROCESS_DATE, > E.TEXT, E.BANK_ORDER_ID, E.CANCELLED_ENTRY_ID, > E.AUDITABLE_ID, E.CANCELLED_ID, E.APPLICATION > ) R1 > LEFT OUTER JOIN AUDITABLE R2 ON R1.AUDITABLE_ID = R2.ID > LEFT OUTER JOIN ENTRY_CONCEPT R3 ON R1.ENTRY_CONCEPT_ID = > R3.ID > ORDER BY > R1.PROCESS_DATE asc, > R1.ID asc [50004-167] > at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) > at org.h2.message.DbException.get(DbException.java:169) > at org.h2.message.DbException.get(DbException.java:146) > at org.h2.command.Parser.parseColumnWithType(Parser.java:3664) > at org.h2.command.Parser.readFunction(Parser.java:2197) > at org.h2.command.Parser.readTerm(Parser.java:2486) > at org.h2.command.Parser.readFactor(Parser.java:2047) > at org.h2.command.Parser.readSum(Parser.java:2037) > at org.h2.command.Parser.readConcat(Parser.java:2007) > at org.h2.command.Parser.readCondition(Parser.java:1872) > at org.h2.command.Parser.readAnd(Parser.java:1853) > at org.h2.command.Parser.readExpression(Parser.java:1845) > at org.h2.command.Parser.readWhen(Parser.java:2682) > at org.h2.command.Parser.readTerm(Parser.java:2480) > at org.h2.command.Parser.readFactor(Parser.java:2047) > at org.h2.command.Parser.readSum(Parser.java:2034) > at org.h2.command.Parser.readConcat(Parser.java:2007) > at org.h2.command.Parser.readCondition(Parser.java:1872) > at org.h2.command.Parser.readAnd(Parser.java:1853) > at org.h2.command.Parser.readExpression(Parser.java:1845) > at org.h2.command.Parser.readTerm(Parser.java:2568) > at org.h2.command.Parser.readFactor(Parser.java:2047) > at org.h2.command.Parser.readSum(Parser.java:2034) > at org.h2.command.Parser.readConcat(Parser.java:2007) > at org.h2.command.Parser.readCondition(Parser.java:1872) > at org.h2.command.Parser.readAnd(Parser.java:1853) > at org.h2.command.Parser.readExpression(Parser.java:1845) > at org.h2.command.Parser.parseSelectSimpleSelectPart(Parser.java:1758) > at org.h2.command.Parser.parseSelectSimple(Parser.java:1790) > at org.h2.command.Parser.parseSelectSub(Parser.java:1685) > at org.h2.command.Parser.parseSelectUnion(Parser.java:1528) > at org.h2.command.Parser.readTableFilter(Parser.java:1027) > at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1691) > at org.h2.command.Parser.parseSelectSimple(Parser.java:1798) > at org.h2.command.Parser.parseSelectSub(Parser.java:1685) > at org.h2.command.Parser.parseSelectUnion(Parser.java:1528) > at org.h2.command.Parser.readTableFilter(Parser.java:1027) > at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1691) > at org.h2.command.Parser.parseSelectSimple(Parser.java:1798) > at org.h2.command.Parser.parseSelectSub(Parser.java:1685) > at org.h2.command.Parser.parseSelectUnion(Parser.java:1528) > at org.h2.command.Parser.parseSelect(Parser.java:1516) > at org.h2.command.Parser.parsePrepared(Parser.java:406) > at org.h2.command.Parser.parse(Parser.java:280) > at org.h2.command.Parser.parse(Parser.java:252) > at org.h2.command.Parser.prepareCommand(Parser.java:218) > at org.h2.engine.Session.prepareLocal(Session.java:415) > at org.h2.engine.Session.prepareCommand(Session.java:364) > at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1111) > at > org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:80) > at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:628) > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:601) > at > com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StmtAcquireTask.run(GooGooStatementCache.java:525) > at > com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547) > > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To view this discussion on the web visit https://groups.google.com/d/msg/h2-database/-/SGD_XDsYNWEJ. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
