Home
Analytics
Using Temporary Tables in Data Set Query
yarrick19
<p>I am testing out the use of BIRT as a reporting tool and connecting to an Amazon Redshift database. When I create a new data set and input the query, I seem to be getting errors if my query uses temporary tables. </p>
<p> </p>
<p>Is this a known limitation of BIRT? Is there any way to get around this that doesn't include using a stored proc (Redshift doesn't allow stored procs at this point in time)? </p>
<p> </p>
<p>BIRT looks like a good tool, but if it can't handle temp tables its usefulness is severely limited.</p>
<p> </p>
<p>Thanks.</p>
Find more posts tagged with
Comments
JFreeman
<p>Are you trying to create and/or write back to the temporary tables?</p>
<p> </p>
<p>Which version of BIRT are you using?</p>
yarrick19
<blockquote class="ipsBlockquote" data-author="JFreeman" data-cid="134445" data-time="1426176501">
<div>
<p>Are you trying to create and/or write back to the temporary tables?</p>
<p> </p>
<p>Which version of BIRT are you using?</p>
</div>
</blockquote>
<p> </p>
<p>First off, thanks for the reply!</p>
<p> </p>
<p>Yes, I'm creating and using temporary tables to do various calculations or business logic that is needed for the report. For example, calculating a z score:</p>
<p> </p>
<div>Select<span> </span>category,</div>
<div>avg(some_number) as avg_some_number,</div>
<div>stddev(some_number) as stdev_some_number</div>
<div>Into<span> </span>#temp</div>
<div>From<span> </span>table1</div>
<div>Group by category;</div>
<div> </div>
<div>Select<span> </span>a.category,</div>
<div>a.some_number,</div>
<div>(a.some_number - b.avg_some_number) / b.stdev_some_number as z_value</div>
<div>From<span> </span>table1 a inner join #temp b</div>
<div>on a.category = b.category;</div>
<div> </div>
<div>I'm using BIRT RCP Report Designer 4_4_1.</div>
JFreeman
<p>Thanks for the additional details.</p>
<p> </p>
<p>Can you provide the full stack trace of the error you are receiving when using a query like this?</p>
yarrick19
<blockquote class="ipsBlockquote" data-author="JFreeman" data-cid="134451" data-time="1426178644">
<div>
<p>Thanks for the additional details.</p>
<p> </p>
<p>Can you provide the full stack trace of the error you are receiving when using a query like this?</p>
</div>
</blockquote>
<p> </p>
<p>Sure. Here are the details of the error message that occurs:</p>
<p> </p>
<div>org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot execute the statement.</div>
<div> org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.</div>
<div>SQL error #1:ERROR: Relation with OID 201057 does not exist.</div>
<div> ;</div>
<div> org.postgresql.util.PSQLException: ERROR: Relation with OID 201057 does not exist.</div>
<div> </div>
<div>at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:52)</div>
<div> </div>
<div>at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108)</div>
<div> </div>
<div>at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:84)</div>
<div> </div>
<div>at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:586)</div>
<div> </div>
<div>at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:970)</div>
<div> </div>
<div>at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:503)</div>
<div> </div>
<div>at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1222)</div>
<div> </div>
<div>at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:233)</div>
<div> </div>
<div>at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:178)</div>
<div> </div>
<div>at org.eclipse.birt.data.engine.impl.QueryResults.getResultMetaData(QueryResults.java:132)</div>
<div> </div>
<div>at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.getRuntimeMetaData(DataSetMetaDataHelper.java:194)</div>
<div> </div>
<div>at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.getRealMetaData(DataSetMetaDataHelper.java:153)</div>
<div> </div>
<div>at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.getDataSetMetaData(DataSetMetaDataHelper.java:115)</div>
<div> </div>
<div>at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.refreshMetaData(DataSetMetaDataHelper.java:382)</div>
<div> </div>
<div>at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.refreshMetaData(DataRequestSessionImpl.java:411)</div>
<div> </div>
<div>at org.eclipse.birt.report.designer.data.ui.dataset.ExternalUIUtil.updateColumnCache(ExternalUIUtil.java:109)</div>
<div> </div>
<div>at org.eclipse.birt.report.designer.data.ui.providers.DefaultDataServiceProvider.updateColumnCache(DefaultDataServiceProvider.java:114)</div>
<div> </div>
<div>at org.eclipse.birt.report.designer.internal.ui.data.DataService.updateColumnCache(DataService.java:140)</div>
<div> </div>
<div>at org.eclipse.birt.report.designer.data.ui.dataset.DataSetUIUtil.updateColumnCache(DataSetUIUtil.java:99)</div>
<div> </div>
<div>at org.eclipse.birt.report.designer.data.ui.dataset.DataSetEditor.okPressed(DataSetEditor.java:687)</div>
<div> </div>
<div>at org.eclipse.birt.report.designer.data.ui.property.AbstractPropertyDialog.buttonPressed(AbstractPropertyDialog.java:650)</div>
<div> </div>
<div>at org.eclipse.jface.dialogs.Dialog$2.widgetSelected(Dialog.java:619)</div>
<div> </div>
<div>at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:248)</div>
<div> </div>
<div>at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)</div>
<div> </div>
<div>at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4353)</div>
<div> </div>
<div>at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1061)</div>
<div> </div>
<div>at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4172)</div>
<div> </div>
<div>at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3761)</div>
<div> </div>
<div>at org.eclipse.jface.window.Window.runEventLoop(Window.java:832)</div>
<div> </div>
<div>at org.eclipse.jface.window.Window.open(Window.java:808)</div>
<div> </div>
<div>at org.eclipse.birt.report.designer.ui.dialogs.BaseDialog.open(BaseDialog.java:111)</div>
<div> </div>
<div>at org.eclipse.birt.report.designer.data.ui.actions.EditDataSetAction.doAction(EditDataSetAction.java:105)</div>
<div> </div>
<div>at org.eclipse.birt.report.designer.internal.ui.views.actions.AbstractElementAction.run(AbstractElementAction.java:70)</div>
<div> </div>
<div>at org.eclipse.jface.action.Action.runWithEvent(Action.java:519)</div>
<div> </div>
<div>at org.eclipse.jface.action.ActionContributionItem.handleWidgetSelection(ActionContributionItem.java:595)</div>
<div> </div>
<div>at org.eclipse.jface.action.ActionContributionItem.access$2(ActionContributionItem.java:511)</div>
<div> </div>
<div>at org.eclipse.jface.action.ActionContributionItem$5.handleEvent(ActionContributionItem.java:420)</div>
<div> </div>
<div>at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)</div>
<div> </div>
<div>at org.eclipse.swt.widgets.Display.sendEvent(Display.java:4353)</div>
<div> </div>
<div>at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1061)</div>
<div> </div>
<div>at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4172)</div>
<div> </div>
<div>at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3761)</div>
<div> </div>
<div>at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$9.run(PartRenderingEngine.java:1151)</div>
<div> </div>
<div>at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)</div>
<div> </div>
<div>at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(PartRenderingEngine.java:1032)</div>
<div> </div>
<div>at org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(E4Workbench.java:148)</div>
<div> </div>
<div>at org.eclipse.ui.internal.Workbench$5.run(Workbench.java:636)</div>
<div> </div>
<div>at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)</div>
<div> </div>
<div>at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:579)</div>
<div> </div>
<div>at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:150)</div>
<div> </div>
<div>at org.eclipse.birt.report.designer.ui.rcp.DesignerApplication.run(DesignerApplication.java:37)</div>
<div> </div>
<div>at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)</div>
<div> </div>
<div>at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)</div>
<div> </div>
<div>at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)</div>
<div> </div>
<div>at java.lang.reflect.Method.invoke(Unknown Source)</div>
<div> </div>
<div>at org.eclipse.equinox.internal.app.EclipseAppContainer.callMethodWithException(EclipseAppContainer.java:587)</div>
<div> </div>
<div>at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:198)</div>
<div> </div>
<div>at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:134)</div>
<div> </div>
<div>at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:104)</div>
<div> </div>
<div>at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:380)</div>
<div> </div>
<div>at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:235)</div>
<div> </div>
<div>at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)</div>
<div> </div>
<div>at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)</div>
<div> </div>
<div>at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)</div>
<div> </div>
<div>at java.lang.reflect.Method.invoke(Unknown Source)</div>
<div> </div>
<div>at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:648)</div>
<div> </div>
<div>at org.eclipse.equinox.launcher.Main.basicRun(Main.java:603)</div>
<div> </div>
<div>at org.eclipse.equinox.launcher.Main.run(Main.java:1465)</div>
<div> </div>
<div>Caused by: org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.</div>
<div>SQL error #1:ERROR: Relation with OID 201057 does not exist.</div>
<div> ;</div>
<div> org.postgresql.util.PSQLException: ERROR: Relation with OID 201057 does not exist.</div>
<div> </div>
<div>at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:481)</div>
<div> </div>
<div>at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doExecuteQuery(OdaQuery.java:480)</div>
<div> </div>
<div>at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.executeQuery(OdaQuery.java:444)</div>
<div> </div>
<div>at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:575)</div>
<div> </div>
<div>... 64 more</div>
<div> </div>
<div>Caused by: org.postgresql.util.PSQLException: ERROR: Relation with OID 201057 does not exist.</div>
<div> </div>
<div>at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2096)</div>
<div> </div>
<div>at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1829)</div>
<div> </div>
<div>at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)</div>
<div> </div>
<div>at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:510)</div>
<div> </div>
<div>at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:386)</div>
<div> </div>
<div>at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)</div>
<div> </div>
<div>at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:477)</div>
<div> </div>
<div>... 67 more</div>
JFreeman
<p>My hunch is that it may be failing to actually write back to the database into the temp table you are specifying which is making the join in the latter query fail.</p>
<p> </p>
<p>Is is possible for you to monitor the temp table creation/update when this query is running to verify if the write back is occurring properly?</p>
yarrick19
<blockquote class="ipsBlockquote" data-author="JFreeman" data-cid="134485" data-time="1426258102">
<div>
<p>My hunch is that it may be failing to actually write back to the database into the temp table you are specifying which is making the join in the latter query fail.</p>
<p> </p>
<p>Is is possible for you to monitor the temp table creation/update when this query is running to verify if the write back is occurring properly?</p>
</div>
</blockquote>
<p> </p>
<p>I think you are correct in that it is not actually writing any data into the temp table on the database. Since a temp table expires when a session ends, I ended up using the same query but tried to write to a permanent table instead of a temp table. Once again, BIRT threw an error, and when exploring the database I don't see that the permanent table was ever created.</p>
<p> </p>
<p>Is there an option or anything that can be configured to allow the query in the data sets to write to the database? Or, is this not supported at all in BIRT?</p>
JFreeman
<p>Out of the box, I do not believe you will be able to write back to the database. The theory is that it is for consuming data versus modifying or creating data. However, I have seen some customizations used before to allow write back to a database.</p>
<p> </p>
<p>Here are a few links to help you get started:</p>
<ol><li><a data-ipb='nomediaparse' href='
http://developer.actuate.com/community/forum/index.php?/topic/34651-write-to-db/'>http://developer.actuate.com/community/forum/index.php?/topic/34651-write-to-db/</a></li>
;
<li><a data-ipb='nomediaparse' href='
http://developer.actuate.com/community/forum/index.php?/files/file/708-birt-report-with-database-write-back/'>http://developer.actuate.com/community/forum/index.php?/files/file/708-birt-report-with-database-write-back/</a></li>
;
<li><a data-ipb='nomediaparse' href='
http://blogs.actuate.com/writing-back-to-a-database-and-other-posts-from-this-week/'>http://blogs.actuate.com/writing-back-to-a-database-and-other-posts-from-this-week/</a></li>
;
</ol>
Hans_vd
<p>Is there a special reason why you want to use a temporary table?</p>
<p>If it is just to keep the queries clear and structured you might consider to rewrite them using a with clause:</p>
<p> </p>
<div style="margin:0px;color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">
<pre class="_prettyXprint">
with my_first_selection as (
Select category,
avg(some_number) as avg_some_number,
stddev(some_number) as stdev_some_number
From table1
Group by category
)
Select a.category,
a.some_number,
(a.some_number - b.avg_some_number) / b.stdev_some_number as z_value
From table1 a inner join my_first_selection b
on a.category = b.category;</pre>
</div>