eclipse
BIRT 4.21 SQL Error on run time when the Alias refers same column in different name
I have the sql for a report something similar to below sql. this report was working fine before upgrade to 4.21. Now throwing error. Due to business reason I had to display the one DB column in 2 different report column. for some reason it is not allowing me to do this. Any help would be greatly appreciated.
SQL : with temp1 as (select i.pk, i.amount as credit_amount from X i) , temp2 as (select i.pk, i.amount as item_amount from X i) select * from temp1 full join temp2 on temp1.pk = temp2.pk;
Run time Error
The new column name or alias AMOUNT is already used by the column at index {1}. at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1060) at org.eclipse.birt.report.engine.executor.ExecutionContext.addException(ExecutionContext.java:1045) at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:83) at org.eclipse.birt.report.engine.executor.TableItemExecutor.execute(TableItemExecutor.java:60) at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.execute(WrappedReportItemExecutor.java:45) at org.eclipse.birt.report.engine.internal.executor.emitter.ReportItemEmitterExecutor.execute(ReportItemEmitterExecutor.java:45) at org.eclipse.birt.report.engine.internal.executor.dup.SuppressDuplicateItemExecutor.execute(SuppressDuplicateItemExecutor.java:41) at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:62) at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:92) at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:97) at org.eclipse.birt.report.engine.presentation.ReportDocumentBuilder.build(ReportDocumentBuilder.java:233) at org.eclipse.birt.report.engine.api.impl.RunTask.doRun(RunTask.java:224) at org.eclipse.birt.report.engine.api.impl.RunTask.run(RunTask.java:85) at org.eclipse.birt.report.service.ReportEngineService.runReport(ReportEngineService.java:1112) at org.eclipse.birt.report.service.BirtViewerReportService.runReport(BirtViewerReportService.java:146) at org.eclipse.birt.report.service.actionhandler.BirtRunReportActionHandler.__execute(BirtRunReportActionHandler.java:79) at org.eclipse.birt.report.service.actionhandler.BirtGetPageActionHandler.__checkDocumentExists(BirtGetPageActionHandler.java:56) at org.eclipse.birt.report.service.actionhandler.AbstractGetPageActionHandler.prepareParameters(AbstractGetPageActionHandler.java:114) at org.eclipse.birt.report.service.actionhandler.AbstractGetPageActionHandler.__execute(AbstractGetPageActionHandler.java:100) at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:88) at org.eclipse.birt.report.soapengine.processor.AbstractBaseDocumentProcessor.__executeAction(AbstractBaseDocumentProcessor.java:45) at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.executeAction(AbstractBaseComponentProcessor.java:132) at org.eclipse.birt.report.soapengine.processor.BirtDocumentProcessor.handleGetPage(BirtDocumentProcessor.java:83) at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103) at java.base/java.lang.reflect.Method.invoke(Method.java:580) at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(AbstractBaseComponentProcessor.java:107) at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(BirtSoapBindingImpl.java:67) at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103) at java.base/java.lang.reflect.Method.invoke(Method.java:580) at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397) at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186) at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:321) at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32) at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118) at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83) at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:420) at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281) at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:689) at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(BirtSoapMessageDispatcherServlet.java:227) at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:590) at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:332) at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:658) at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:115) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:195) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) at org.eclipse.birt.report.filter.ViewerFilter.doFilter(ViewerFilter.java:71) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) at com.carreker.reportviewer.common.RequestFilter.doFilter(RequestFilter.java:427) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) at com.carreker.reportviewer.common.SecureEAMLoggerFilter.doFilter(SecureEAMLoggerFilter.java:212) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) at org.owasp.csrfguard.http.FilterChainWrapper.doFilter(FilterChainWrapper.java:42) at org.owasp.csrfguard.CsrfGuardFilter.doFilter(CsrfGuardFilter.java:174) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) at com.carreker.reportviewer.action.ApplicationFilter.doFilter(ApplicationFilter.java:42) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) at com.carreker.reportviewer.common.ExceptionFilter.doFilter(ExceptionFilter.java:105) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) at org.apache.logging.log4j.web.Log4jServletFilter.doFilter(Log4jServletFilter.java:70) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:164) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:140) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:483) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:116) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:666) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:398) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:903) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1776) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52) at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:975) at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:493) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63) at java.base/java.lang.Thread.run(Thread.java:1583) Caused by: org.eclipse.birt.report.data.adapter.api.AdapterException: An exception occurred during processing. Please see the following message for details: Failed to prepare the query execution for the data set: Daily Rejects The new column name or alias AMOUNT is already used by the column at index {1}. at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:607) at org.eclipse.birt.report.engine.data.dte.DteDataEngine.doExecuteQuery(DteDataEngine.java:138) at org.eclipse.birt.report.engine.data.dte.DataGenerationEngine.doExecuteQuery(DataGenerationEngine.java:74) at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:254) at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1624) at org.eclipse.birt.report.engine.executor.QueryItemExecutor.executeQuery(QueryItemExecutor.java:73) ... 84 more Caused by: org.eclipse.birt.data.engine.core.DataException: Failed to prepare the query execution for the data set: Daily Rejects The new column name or alias AMOUNT is already used by the column at index {1}. at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:352) at org.eclipse.birt.data.engine.impl.PreparedQuery.doPrepare(PreparedQuery.java:388) at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.produceQueryResults(PreparedDataSourceQuery.java:164) at org.eclipse.birt.data.engine.impl.PreparedDataSourceQuery.execute(PreparedDataSourceQuery.java:153) at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery.execute(PreparedOdaDSQuery.java:172) at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.execute(DataRequestSessionImpl.java:594) ... 89 more Caused by: org.eclipse.birt.data.engine.odaconsumer.OdaDataException: The new column name or alias AMOUNT is already used by the column at index {1}. at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:46) at org.eclipse.birt.data.engine.odaconsumer.ProjectedColumns.checkColumnsNaming(ProjectedColumns.java:372) at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.checkColumnsNaming(PreparedStatement.java:4476) at org.eclipse.birt.data.engine.executor.DataSourceQuery.addColumnHints(DataSourceQuery.java:658) at org.eclipse.birt.data.engine.executor.DataSourceQuery.prepareColumns(DataSourceQuery.java:388) at org.eclipse.birt.data.engine.executor.DataSourceQuery.prepare(DataSourceQuery.java:336) at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.prepareOdiQuery(PreparedOdaDSQuery.java:565) at org.eclipse.birt.data.engine.impl.QueryExecutor.prepareExecution(QueryExecutor.java:350) ... 94 more
Did you try e.g. adding a blank to the SQL statement, to force BIRT to parse it again? I mean it is strange that the error message mentions a column "AMOUNT" when your statement does not produce such a column.
OTOH your select statement is crap anyway, or you didn't show us the actual statement.
- The semicolon does not belong there.
- Dont use
select * from- don't do this except for quick and dirty ad-hoc queries. In this case your query should output 4 columns, probably not what you intended. Did you test your query outside of BIRT? - Your statement could be simply
select X.pk, X.amount as credit_amount, X.pk as item_amount from X
I tried by adding BLANK SQL statement. execution did not throw any error this time. No records in the report as expected. Yes removed the semicolon, that was by mistake. Also replaced the select * with column names. Issue getting the same issue for 3 columns . When i replace with random instead of same column report execution were successful.
I have started a small test with the latest BIRT version 4.22 with the mentioned statement type.
At first, it is good approach to use the "*" to define the columns from a statement because you have no control of columns and you have no handling about your columns names. The result of the column names is also dependend of the used db-driver. Therefore the best fix would be to use named columns according to the hint of @hvbtup .
So my test based on MS SQL server, I created a CTE-SQL-statement with the same join like you - screen is following. My problem is, I didn't got your error. For further investigation it would be good to have an example of your report without external dependencies to check further details.
Test Report - CTE-SQL
BIRT preview result
The ticket will be closed when a test report is provided then it can be reopened.