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/-/hrBSfNIlplgJ.
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.
