nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

oracle date parameter

404040

<p>Hi i am using a oracle databases i had some error in passing parameter to date function</p><p> </p><p>let u consider like Report parameter like params["select month"].value ,</p><p><strong>Value column is TRAN_DATE and Display coulmn must be like</strong></p><div> </div><div>01-Mar-2013 12:00 am<span> </span> Mar</div><div>01-Mar-2013 12:00 am<span> </span> Apr</div><div>01-Mar-2013 12:00 am<span> </span> May</div><div>01-Mar-2013 12:00 am<span> </span> ..</div><div>01-Mar-2013 12:00 am<span> </span> ...</div><div>01-Mar-2013 12:00 am<span> </span> Dec</div><div> </div><div>it is in dataset like <strong>Month</strong>::</div><div><div> </div><div>select distinct </div><div>TRAN_DATE </div><div>from OUTLET_PAYMODE_REPORT_FACT</div><div> </div><div>Now i have to pass parameter to this main data-set so i created four variable </div><div> </div></div><div><strong>main dataset::</strong></div><div> </div><div><div>select TRAN_DATE,</div><div>trunc(nvl(SUM(SALES_VALUE),0)+nvl(sum(total_sales),0)-nvl(sum(net_sales),0)+nvl(sum(discount),0)) Sale</div><div>from OUTLET_PAYMODE_REPORT_FACT A,OUTLET_DETAILS B</div><div>WHERE A.OUTLET_ID=B.OUTLET_ID and SALES_VALUE>0</div><div>and to_date(TRAN_DATE, 'yyyy') = ? AND to_date(TRAN_DATE, 'MON') = ?</div><div>OR to_date(TRAN_DATE, 'yyyy') = ? AND to_date(TRAN_DATE, 'MON') = ?</div><div>GROUP BY TRAN_DATE</div><div> </div><div>if i manually append value in some different format is works </div><div> </div><div><div>select TRAN_DATE,</div><div>trunc(nvl(SUM(SALES_VALUE),0)+nvl(sum(total_sales),0)-nvl(sum(net_sales),0)+nvl(sum(discount),0)) Sale</div><div>from OUTLET_PAYMODE_REPORT_FACT A,OUTLET_DETAILS B</div><div>WHERE A.OUTLET_ID=B.OUTLET_ID and SALES_VALUE>0</div><div>and (to_char(TRAN_DATE, 'yyyy') = '2013' AND to_char(TRAN_DATE, 'MON') = 'APR')</div><div>OR (to_char(TRAN_DATE, 'yyyy') = '2013' AND to_char(TRAN_DATE, 'MON') = 'MAR')</div><div>GROUP BY TRAN_DATE</div><div> </div><div> </div><div>But my situation now is the Variable are in Date format and parameter are in Date Format so dataset appending value to must be date format so how to achieve this??</div></div><div> </div><div>Kindly some one look at this report resolve this oracle date issue i had problem for more than a week </div><div> </div><div><div><pre class="_prettyXprint">org.eclipse.birt.report.engine.api.EngineException: Error happened while running the report.at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.doRun(DatasetPreviewTask.java:318)at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.runDataset(DatasetPreviewTask.java:280)at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.execute(DatasetPreviewTask.java:91)at org.eclipse.birt.report.designer.data.ui.dataset.DataSetPreviewer.preview(DataSetPreviewer.java:68)at org.eclipse.birt.report.designer.data.ui.dataset.ResultSetPreviewPage$5.run(ResultSetPreviewPage.java:366)at org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)Caused by: org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot get the result set metadata. org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.SQL error #1:ORA-01830: date format picture ends before converting entire input string ; java.sql.SQLException: ORA-01830: date format picture ends before converting entire input stringat org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:52)at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108)at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:84)at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:414)at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getProjectedColumns(PreparedStatement.java:377)at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.doGetMetaData(PreparedStatement.java:347)at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:563)at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:972)at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:503)at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1208)at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:233)at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:178)at org.eclipse.birt.data.engine.impl.QueryResults.getResultMetaData(QueryResults.java:132)at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.extractQuery(DatasetPreviewTask.java:352)at org.eclipse.birt.report.engine.api.impl.DatasetPreviewTask.doRun(DatasetPreviewTask.java:309)... 5 moreCaused by: org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.SQL error #1:ORA-01830: date format picture ends before converting entire input string ; java.sql.SQLException: ORA-01830: date format picture ends before converting entire input stringat org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:481)at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaUsingPolicy1(Statement.java:420)at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaData(Statement.java:316)at org.eclipse.birt.report.data.oda.jdbc.bidi.BidiStatement.getMetaData(BidiStatement.java:56)at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doGetMetaData(OdaQuery.java:423)at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.getMetaData(OdaQuery.java:390)at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:407)... 16 moreCaused by: java.sql.SQLException: ORA-01830: date format picture ends before converting entire input stringat oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.java:1198)at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2400)at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672)at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527)at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:477)... 22 more</pre></div><div> </div></div></div>

Find more posts tagged with

Comments

micajblock

<p>Forget about the Oracle dates for a moment and explain what you are trying to accomplish with the two parameters 'select month' and 'period'? What do you want to filter the query on?</p>

404040

<div>Thanks u r reply now am trying to achieve is my query have to return like if "period" is "last month" i want to return data for Perivous Month of params["select month"] selected and if "period" is "last Year" i want to return data for Perivous year of params["select month"] selected .. </div><div> </div><div>for example :::</div><div> params["select month"] is jan 2012 and period like "last month" i want to query return for pervious month i.e dec 2011 like wise for "Year"</div><div> </div><div> </div><div>Its work in classic models example but i am trying to oracle but it cant work???</div>

Hans_vd

<p>Let me get back to your original query</p><p> </p><blockquote class="ipsBlockquote"><pre class="_prettyXprint">select TRAN_DATE,trunc(nvl(SUM(SALES_VALUE),0)+nvl(sum(total_sales),0)-nvl(sum(net_sales),0)+nvl(sum(discount),0)) Salefrom OUTLET_PAYMODE_REPORT_FACT A,OUTLET_DETAILS BWHERE A.OUTLET_ID=B.OUTLET_ID and SALES_VALUE>0and (to_char(TRAN_DATE, 'yyyy') = '2013' AND to_char(TRAN_DATE, 'MON') = 'APR')OR (to_char(TRAN_DATE, 'yyyy') = '2013' AND to_char(TRAN_DATE, 'MON') = 'MAR')GROUP BY TRAN_DATE</pre></blockquote><p> </p><p> </p><p>If I understood well, the parameters are of type date, but when you enter the values manually, you are using strings.</p><p>If the parameters are dates, you have to treat them like dates, so you should write something like this:</p><pre class="_prettyXprint">... to_char(TRAN_DATE, 'yyyy') = to_char(?, 'yyyy') AND to_char(TRAN_DATE, 'MON') = to_char(?, 'MON') ...</pre><p>Second, when doing an OR relation you have to be very careful with the brackets. This would be correct:</p><pre class="_prettyXprint">and ((to_char(TRAN_DATE, 'yyyy') = to_char(?, 'yyyy') AND to_char(TRAN_DATE, 'MON') = to_char(?, 'MON'))OR (to_char(TRAN_DATE, 'yyyy') = to_char(?, 'yyyy') AND to_char(TRAN_DATE, 'MON') = to_char(?, 'MON')))</pre><p>Hope this helps</p>

GLO_FR

<p>Hi,</p><p> </p><p>Here is an other solution with simple SQL orders.</p><p>That is what you want </p><p>[color=rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;font-size:14px;] 1) if "period" is "last month", query returns data for Previous Month of params["select month"] selected [/color]</p><p>[color=rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;font-size:14px;] 2) if "period" is "last Year", [/color][color=rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;]query returns[/color][color=rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;font-size:14px;] data for [/color][color=rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;]Previous [/color][color=rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;font-size:14px;]year of params["select month"] selected[/color]</p><p> </p><p>Keep the parameter period with 2 entries : "last year" and "last mont"</p><p>the parameter "select month" <strong>value </strong>of the first day (or last if you want) of your TRANS_DATE, and you can use the <strong>display </strong>value of your choice</p><p> </p><div><pre class="_prettyXprint">SELECT ......FROM OUTLET_PAYMODE_REPORT_FACT A,OUTLET_DETAILS BWHERE A.OUTLET_ID = B.OUTLET_ID AND SALES_VALUE > 0AND ( ('last month' = ? AND MONTH(TRAN_DATE)+1 = ? AND YEAR(TRAN_DATE) = ? ) OR ('last year' = ? AND MONTH(TRAN_DATE) = ? AND YEAR(TRAN_DATE)+1 = ? ) ) GROUP BY ...</pre></div><div>Then bind query parameters like this</div><div>1) linked to report parameter : period</div><div>2) BirtDateTime.month(params["select month"].value)</div><div>3) BirtDateTime.year(params["select month"].value)</div><div>4) linked to report parameter : period</div><div>5) BirtDateTime.month(params["select month"].value)</div><div>6) BirtDateTime.year(params["select month"].value)</div>

404040

<p>@Hans_vd</p><p>Thanks for u r reply it unfortunately cant work kindly look at this screenshot for reference </p><p> </p><p>1)date screenshot --->contain date only it is format Date time format</p><p>2)first parameter screenshot---> contain Tran_date column as date value and display</p><p>3)Append screenshot--->while appending a variable to query </p><p>4) main screenshot --->query with parameter passing</p><p>5)preview screenshot--->showing some other error like</p><p> </p><p>It cant work wt happening i could't found solution kindly help on this???</p>

404040

<p>@GLO_FR yes you understood my requirement is correct but if i tried with u r solution it gives some error like </p><p> </p><div><pre class="_prettyXprint">org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot get the result set metadata. org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.SQL error #1:ORA-00904: "MONTH": invalid identifier ; java.sql.SQLException: ORA-00904: "MONTH": invalid identifierat org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:52)at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108)at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:84)at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:414)at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getProjectedColumns(PreparedStatement.java:377)at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.doGetMetaData(PreparedStatement.java:347)at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:563)at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:972)at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:503)at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1208)at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:233)at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:178)at org.eclipse.birt.data.engine.impl.QueryResults.getResultMetaData(QueryResults.java:132)at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.getRuntimeMetaData(DataSetMetaDataHelper.java:194)at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.getRealMetaData(DataSetMetaDataHelper.java:153)at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.getDataSetMetaData(DataSetMetaDataHelper.java:115)at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.getDataSetMetaData(DataRequestSessionImpl.java:240)at org.eclipse.birt.report.designer.data.ui.util.DataSetProvider.populateAllOutputColumns(DataSetProvider.java:221)at org.eclipse.birt.report.designer.data.ui.util.DataSetProvider.getColumns(DataSetProvider.java:177)at org.eclipse.birt.report.designer.data.ui.dataset.DataSetEditor$ItemModelManager.getCurrentItemModel(DataSetEditor.java:1095)at org.eclipse.birt.report.designer.data.ui.dataset.DataSetEditor.getCurrentItemModel(DataSetEditor.java:635)at org.eclipse.birt.report.designer.data.ui.dataset.DataSetComputedColumnsPage.refreshColumnNames(DataSetComputedColumnsPage.java:310)at org.eclipse.birt.report.designer.data.ui.dataset.DataSetComputedColumnsPage.pageActivated(DataSetComputedColumnsPage.java:287)at org.eclipse.birt.report.designer.data.ui.property.AbstractPropertyDialog.showPage(AbstractPropertyDialog.java:588)at org.eclipse.birt.report.designer.data.ui.property.AbstractPropertyDialog.showSelectionPage(AbstractPropertyDialog.java:482)at org.eclipse.birt.report.designer.data.ui.dataset.DataSetEditor.showSelectionPage(DataSetEditor.java:913)at org.eclipse.birt.report.designer.data.ui.property.AbstractPropertyDialog$2$1.run(AbstractPropertyDialog.java:438)at org.eclipse.swt.custom.BusyIndicator.showWhile(BusyIndicator.java:70)at org.eclipse.birt.report.designer.data.ui.property.AbstractPropertyDialog$2.selectionChanged(AbstractPropertyDialog.java:433)at org.eclipse.jface.viewers.Viewer$2.run(Viewer.java:164)at org.eclipse.core.runtime.SafeRunner.run(SafeRunner.java:42)at org.eclipse.ui.internal.JFaceUtil$1.run(JFaceUtil.java:49)at org.eclipse.jface.util.SafeRunnable.run(SafeRunnable.java:175)at org.eclipse.jface.viewers.Viewer.fireSelectionChanged(Viewer.java:162)at org.eclipse.jface.viewers.StructuredViewer.updateSelection(StructuredViewer.java:2188)at org.eclipse.jface.viewers.StructuredViewer.handleSelect(StructuredViewer.java:1211)at org.eclipse.jface.viewers.StructuredViewer$4.widgetSelected(StructuredViewer.java:1241)at org.eclipse.jface.util.OpenStrategy.fireSelectionEvent(OpenStrategy.java:239)at org.eclipse.jface.util.OpenStrategy.access$4(OpenStrategy.java:233)at org.eclipse.jface.util.OpenStrategy$1.handleEvent(OpenStrategy.java:403)at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1392)at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3742)at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3363)at org.eclipse.jface.window.Window.runEventLoop(Window.java:826)at org.eclipse.jface.window.Window.open(Window.java:802)at org.eclipse.birt.report.designer.ui.dialogs.BaseDialog.open(BaseDialog.java:111)at org.eclipse.birt.report.designer.data.ui.providers.EditableDataSetNodeProvider.performEdit(EditableDataSetNodeProvider.java:102)at org.eclipse.birt.report.designer.internal.ui.views.DefaultNodeProvider.performRequest(DefaultNodeProvider.java:472)at org.eclipse.birt.report.designer.internal.ui.views.actions.EditAction.doAction(EditAction.java:85)at org.eclipse.birt.report.designer.internal.ui.views.actions.AbstractElementAction.run(AbstractElementAction.java:70)at org.eclipse.birt.report.designer.internal.ui.views.RenameListener.doubleClick(RenameListener.java:214)at org.eclipse.jface.viewers.StructuredViewer$1.run(StructuredViewer.java:845)at org.eclipse.core.runtime.SafeRunner.run(SafeRunner.java:42)at org.eclipse.ui.internal.JFaceUtil$1.run(JFaceUtil.java:49)at org.eclipse.jface.util.SafeRunnable.run(SafeRunnable.java:175)at org.eclipse.jface.viewers.StructuredViewer.fireDoubleClick(StructuredViewer.java:843)at org.eclipse.jface.viewers.AbstractTreeViewer.handleDoubleSelect(AbstractTreeViewer.java:1477)at org.eclipse.jface.viewers.StructuredViewer$4.widgetDefaultSelected(StructuredViewer.java:1246)at org.eclipse.jface.util.OpenStrategy.fireDefaultSelectionEvent(OpenStrategy.java:249)at org.eclipse.jface.util.OpenStrategy.access$0(OpenStrategy.java:246)at org.eclipse.jface.util.OpenStrategy$1.handleEvent(OpenStrategy.java:307)at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1392)at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3742)at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3363)at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$9.run(PartRenderingEngine.java:1113)at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(PartRenderingEngine.java:997)at org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(E4Workbench.java:138)at org.eclipse.ui.internal.Workbench$5.run(Workbench.java:610)at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:567)at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:150)at org.eclipse.ui.internal.ide.application.IDEApplication.start(IDEApplication.java:124)at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110)at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79)at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:354)at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:181)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:606)at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:636)at org.eclipse.equinox.launcher.Main.basicRun(Main.java:591)at org.eclipse.equinox.launcher.Main.run(Main.java:1450)Caused by: org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.SQL error #1:ORA-00904: "MONTH": invalid identifier ; java.sql.SQLException: ORA-00904: "MONTH": invalid identifierat org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:481)at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaUsingPolicy1(Statement.java:420)at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaData(Statement.java:316)at org.eclipse.birt.report.data.oda.jdbc.bidi.BidiStatement.getMetaData(BidiStatement.java:56)at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doGetMetaData(OdaQuery.java:423)at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.getMetaData(OdaQuery.java:390)at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:407)... 83 moreCaused by: java.sql.SQLException: ORA-00904: "MONTH": invalid identifierat oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:830)at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2391)at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672)at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527)at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:477)... 89 more</pre></div>

GLO_FR

<p>I think for ORACLE, to extract month value it will be <span style="font-size:13px;font-style:inherit;font-weight:inherit;">EXTRACT(MONTH FROM TRANS_DATE)... same for year</span></p>

404040

<p>[color=rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;]@GLO_FR[/color]ya that trick may work but while preview dataset no error but while preview in browser it show error like (error screenshot)</p><p> </p><p> </p>

GLO_FR

<p>param2, param3, param5, param6 have to be integer</p>

404040

<p>no my date format is dd-MON-YYYY so my month to be string or integer</p><p> </p><p>Date:::</p><p> </p><p>TRAN_DATE<span> </span></p><div>01-Mar-2012 12:00 am<span> </span><span> </span><span> </span></div><div>02-Mar-2012 12:00 am<span> </span><span> </span></div><div>03-Mar-2012 12:00 am<span> </span><span> </span></div><div>04-Mar-2012 12:00 am<span> </span><span> </span></div><div>05-Mar-2012 12:00 am<span> </span><span> </span></div>

GLO_FR

<p>BirtDateTime.month(params["select month"].value) returns an integer and you compare this value in your query with <span style="font-size:14px;color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"> </span>[color=rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;font-style:inherit;font-weight:inherit;font-size:13px;]EXTRACT(MONTH FROM TRANS_DATE) which also returns integer[/color]</p><p> </p><p> </p><p> </p><p> </p><p> </p>

404040

<p>fine but if i use <span style="font-size:14px;color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"> </span>[color=rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;font-style:inherit;font-weight:inherit;font-size:13px;]EXTRACT(MONTH FROM TRANS_DATE) return only decimal look at this screenshot??[/color]</p>

GLO_FR

<p>This is your report. I can't run it because I don't have access to your db but it should work</p>

404040

<p>thanks for u r all effort but i removed a computed column in "date to display" because it show some error (compute column screenshot)and then i run the query with chart it show like this screenshot(output)</p><p> </p><p>In the output screen my excepted result be like May-2012 one bar and Apr-2012 another bar.but here it shown only one bar i dono y. how to check the query execute correctly or not</p><p> </p>

GLO_FR

<p>The query only return values form one month (it's what you wanted)</p><p> </p><p>If you select "last month" + April 2012, query returns data for March 2012</p><p>If you select "last month" + Feb 2012, query returns data for Jan 2012</p><p>etc</p><p>If you select "last year" + April 2012, query returns data for April 2011</p><p>If you select "last year" + Feb 2012, query returns data for Feb 2011</p><p> </p><p>BTW, intead of </p><pre class="_prettyXprint">AND ( ('last month' = ? AND EXTRACT(MONTH FROM TRANS_DATE) +1 = ? AND EXTRACT(YEAR FROM TRANS_DATE) = ? ) OR ('last year' = ? AND EXTRACT(MONTH FROM TRANS_DATE) = ? AND EXTRACT(YEAR FROM TRANS_DATE) +1 = ? ) )</pre><p><span>use (or you will have problem for december/january)</span></p><pre class="_prettyXprint">AND ( ('last month' = ? AND EXTRACT(MONTH FROM ADD_MONTH(TRAN_DATE,1)) = ? AND EXTRACT(YEAR FROM ADD_MONTH(TRAN_DATE,1)) = ? ) OR ('last year' = ? AND EXTRACT(MONTH FROM TRANS_DATE) = ? AND EXTRACT(YEAR FROM TRANS_DATE) +1 = ? ) )</pre>

404040

<p>no .[color=rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;font-size:14px;]The query have to return values like[/color]</p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">(i.e)</p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">If you select "last month" + April 2012, query returns data for March 2012 and <span style="font-size:14px;">April 2012</span></p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">If you select "last month" + Feb 2012, query returns data for Jan 2012 and <span style="font-size:14px;">Feb 2012</span></p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">etc</p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">If you select "last year" + April 2012, query returns data for April 2011 and <span style="font-size:14px;">April 2012</span></p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">If you select "last year" + Feb 2012, query returns data for Feb 2011 and <span style="font-size:14px;">Feb 2012</span></p>

GLO_FR

<p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"><span style="font-size:14px;">So intead of </span></p><pre class="_prettyXprint">AND ( ('last month' = ? AND EXTRACT(MONTH FROM ADD_MONTH(TRAN_DATE,1)) = ? AND EXTRACT(YEAR FROM ADD_MONTH(TRAN_DATE,1)) = ? ) OR ('last year' = ? AND EXTRACT(MONTH FROM TRANS_DATE) = ? AND EXTRACT(YEAR FROM TRANS_DATE) +1 = ? ) )</pre><div>use</div><div><pre class="_prettyXprint">AND ( ( ('last month' = ? AND EXTRACT(MONTH FROM ADD_MONTH(TRAN_DATE,1)) = ? AND EXTRACT(YEAR FROM ADD_MONTH(TRAN_DATE,1)) = ? ) OR ('last year' = ? AND EXTRACT(MONTH FROM TRANS_DATE) = ? AND EXTRACT(YEAR FROM TRANS_DATE)+1 = ? ) ) OR (EXTRACT(MONTH FROM TRANS_DATE) = ? AND EXTRACT(YEAR FROM TRANS_DATE) = ? ) )</pre><p>in this case, you'll have 2 new parameters</p><div style="margin:0px;color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">7) <strong>integer </strong>- BirtDateTime.month(params["select month"].value)</div><div style="margin:0px;color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">8) <strong>integer </strong>- BirtDateTime.year(params["select month"].value)</div><p> </p></div>

404040

<p>fine i will try and say ...i am searching this in oracle <strong><span style="font-size:14px;">MONTH</span><span style="font-size:14px;color:rgb(102,102,0);">(</span><span style="font-size:14px;">ADD_MONTH</span><span style="font-size:14px;color:rgb(102,102,0);">(</span><span style="font-size:14px;">TRAN_DATE</span><span style="font-size:14px;color:rgb(102,102,0);">,</span><span style="font-size:14px;color:rgb(0,102,102);">1</span><span style="font-size:14px;color:rgb(102,102,0);">)) </span></strong></p>

GLO_FR

<p>uups, sorry ADD_MONTH[color=#ff0000;]<strong>S</strong>[/color]</p><pre class="_prettyXprint">AND ( ( ('last month' = ? AND EXTRACT(MONTH FROM ADD_MONTHS(TRAN_DATE,1)) = ? AND EXTRACT(YEAR FROM ADD_MONTHS(TRAN_DATE,1)) = ? ) OR ('last year' = ? AND EXTRACT(MONTH FROM TRANS_DATE) = ? AND EXTRACT(YEAR FROM TRANS_DATE)+1 = ? ) ) OR (EXTRACT(MONTH FROM TRANS_DATE) = ? AND EXTRACT(YEAR FROM TRANS_DATE) = ? ) )</pre>

404040

<div>i had tried with u r suggestion at-last but its not work i will try and said </div><div> </div><div>oracle query::</div><div><div><pre class="_prettyXprint">select TRAN_DATE,trunc(nvl(SUM(SALES_VALUE),0)+nvl(sum(total_sales),0)-nvl(sum(net_sales),0)+nvl(sum(discount),0)) Salefrom OUTLET_PAYMODE_REPORT_FACT A,OUTLET_DETAILS BWHERE A.OUTLET_ID=B.OUTLET_ID and SALES_VALUE>0 AND ((('last month' = ? AND EXTRACT(MONTH FROM ADD_MONTHS( tran_date,1)) = ? AND EXTRACT(YEAR FROM ADD_MONTHS(tran_date,1)) = ?) OR ('last year'= ? AND EXTRACT(MONTH FROM TRAN_DATE) = ? AND EXTRACT(YEAR FROM TRAN_DATE)+1 = ?) )OR EXTRACT(MONTH FROM TRAN_DATE) = ? AND EXTRACT(YEAR FROM TRAN_DATE) = ?))GROUP BY TRAN_DATE</pre></div><div>error like::</div><div> </div><div><div><pre class="_prettyXprint">org.eclipse.birt.data.engine.odaconsumer.OdaDataException: Cannot get the result set metadata. org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.SQL error #1:ORA-00933: SQL command not properly ended ; java.sql.SQLException: ORA-00933: SQL command not properly endedat org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.newException(ExceptionHandler.java:52)at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:108)at org.eclipse.birt.data.engine.odaconsumer.ExceptionHandler.throwException(ExceptionHandler.java:84)at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:414)at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getProjectedColumns(PreparedStatement.java:377)at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.doGetMetaData(PreparedStatement.java:347)at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.execute(PreparedStatement.java:563)at org.eclipse.birt.data.engine.executor.DataSourceQuery.execute(DataSourceQuery.java:972)at org.eclipse.birt.data.engine.impl.PreparedOdaDSQuery$OdaDSQueryExecutor.executeOdiQuery(PreparedOdaDSQuery.java:503)at org.eclipse.birt.data.engine.impl.QueryExecutor.execute(QueryExecutor.java:1208)at org.eclipse.birt.data.engine.impl.ServiceForQueryResults.executeQuery(ServiceForQueryResults.java:233)at org.eclipse.birt.data.engine.impl.QueryResults.getResultIterator(QueryResults.java:178)at org.eclipse.birt.data.engine.impl.QueryResults.getResultMetaData(QueryResults.java:132)at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.getRuntimeMetaData(DataSetMetaDataHelper.java:194)at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.getRealMetaData(DataSetMetaDataHelper.java:153)at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.getDataSetMetaData(DataSetMetaDataHelper.java:115)at org.eclipse.birt.report.data.adapter.impl.DataSetMetaDataHelper.refreshMetaData(DataSetMetaDataHelper.java:382)at org.eclipse.birt.report.data.adapter.impl.DataRequestSessionImpl.refreshMetaData(DataRequestSessionImpl.java:411)at org.eclipse.birt.report.designer.data.ui.dataset.ExternalUIUtil.updateColumnCache(ExternalUIUtil.java:109)at org.eclipse.birt.report.designer.data.ui.providers.DefaultDataServiceProvider.updateColumnCache(DefaultDataServiceProvider.java:114)at org.eclipse.birt.report.designer.internal.ui.data.DataService.updateColumnCache(DataService.java:140)at org.eclipse.birt.report.designer.data.ui.dataset.DataSetUIUtil.updateColumnCache(DataSetUIUtil.java:99)at org.eclipse.birt.report.designer.data.ui.dataset.DataSetEditor.okPressed(DataSetEditor.java:687)at org.eclipse.birt.report.designer.data.ui.property.AbstractPropertyDialog.buttonPressed(AbstractPropertyDialog.java:650)at org.eclipse.jface.dialogs.Dialog$2.widgetSelected(Dialog.java:628)at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:248)at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1392)at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3742)at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3363)at org.eclipse.jface.window.Window.runEventLoop(Window.java:826)at org.eclipse.jface.window.Window.open(Window.java:802)at org.eclipse.birt.report.designer.ui.dialogs.BaseDialog.open(BaseDialog.java:111)at org.eclipse.birt.report.designer.data.ui.providers.EditableDataSetNodeProvider.performEdit(EditableDataSetNodeProvider.java:102)at org.eclipse.birt.report.designer.internal.ui.views.DefaultNodeProvider.performRequest(DefaultNodeProvider.java:472)at org.eclipse.birt.report.designer.internal.ui.views.actions.EditAction.doAction(EditAction.java:85)at org.eclipse.birt.report.designer.internal.ui.views.actions.AbstractElementAction.run(AbstractElementAction.java:70)at org.eclipse.birt.report.designer.internal.ui.views.RenameListener.doubleClick(RenameListener.java:214)at org.eclipse.jface.viewers.StructuredViewer$1.run(StructuredViewer.java:845)at org.eclipse.core.runtime.SafeRunner.run(SafeRunner.java:42)at org.eclipse.ui.internal.JFaceUtil$1.run(JFaceUtil.java:49)at org.eclipse.jface.util.SafeRunnable.run(SafeRunnable.java:175)at org.eclipse.jface.viewers.StructuredViewer.fireDoubleClick(StructuredViewer.java:843)at org.eclipse.jface.viewers.AbstractTreeViewer.handleDoubleSelect(AbstractTreeViewer.java:1477)at org.eclipse.jface.viewers.StructuredViewer$4.widgetDefaultSelected(StructuredViewer.java:1246)at org.eclipse.jface.util.OpenStrategy.fireDefaultSelectionEvent(OpenStrategy.java:249)at org.eclipse.jface.util.OpenStrategy.access$0(OpenStrategy.java:246)at org.eclipse.jface.util.OpenStrategy$1.handleEvent(OpenStrategy.java:307)at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1392)at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3742)at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3363)at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine$9.run(PartRenderingEngine.java:1113)at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)at org.eclipse.e4.ui.internal.workbench.swt.PartRenderingEngine.run(PartRenderingEngine.java:997)at org.eclipse.e4.ui.internal.workbench.E4Workbench.createAndRunUI(E4Workbench.java:138)at org.eclipse.ui.internal.Workbench$5.run(Workbench.java:610)at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:567)at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:150)at org.eclipse.ui.internal.ide.application.IDEApplication.start(IDEApplication.java:124)at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110)at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79)at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:354)at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:181)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:606)at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:636)at org.eclipse.equinox.launcher.Main.basicRun(Main.java:591)at org.eclipse.equinox.launcher.Main.run(Main.java:1450)Caused by: org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.SQL error #1:ORA-00933: SQL command not properly ended ; java.sql.SQLException: ORA-00933: SQL command not properly endedat org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:481)at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaUsingPolicy1(Statement.java:420)at org.eclipse.birt.report.data.oda.jdbc.Statement.getMetaData(Statement.java:316)at org.eclipse.birt.report.data.oda.jdbc.bidi.BidiStatement.getMetaData(BidiStatement.java:56)at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.doGetMetaData(OdaQuery.java:423)at org.eclipse.datatools.connectivity.oda.consumer.helper.OdaQuery.getMetaData(OdaQuery.java:390)at org.eclipse.birt.data.engine.odaconsumer.PreparedStatement.getRuntimeMetaData(PreparedStatement.java:407)... 69 moreCaused by: java.sql.SQLException: ORA-00933: SQL command not properly endedat oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:830)at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2391)at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672)at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527)at org.eclipse.birt.report.data.oda.jdbc.Statement.executeQuery(Statement.java:477)... 75 more</pre></div><div> </div></div></div><p> </p>

GLO_FR

<p>The error message is clear</p><pre class="_prettyXprint">SQL error #1:ORA-00933: SQL command not properly ended</pre><pre class="_prettyXprint">select TRAN_DATE,trunc(nvl(SUM(SALES_VALUE),0)+nvl(sum(total_sales),0)-nvl(sum(net_sales),0)+nvl(sum(discount),0)) Salefrom OUTLET_PAYMODE_REPORT_FACT A,OUTLET_DETAILS BWHERE A.OUTLET_ID=B.OUTLET_ID and SALES_VALUE>0 AND ( ( ('last month' = ? AND EXTRACT(MONTH FROM ADD_MONTHS( tran_date,1)) = ? AND EXTRACT(YEAR FROM ADD_MONTHS(tran_date,1)) = ?) OR ('last year'= ? AND EXTRACT(MONTH FROM TRAN_DATE) = ? AND EXTRACT(YEAR FROM TRAN_DATE)+1 = ?) )OR (EXTRACT(MONTH FROM TRAN_DATE) = ? AND EXTRACT(YEAR FROM TRAN_DATE) = ?))GROUP BY TRAN_DATE</pre><p>one parenthesis was missing</p>

micajblock

<p>I would do this differently. I would have my where clause simple like this :</p><pre class="_prettyXprint">MONTH(TRAN_DATE) = ? AND YEAR(TRAN_DATE) = ?</pre><p>Then use the default value of the parameter to put script that would do the date calculations.</p>

GLO_FR

<p>Hi Mica,</p><p> </p><p>I aggree, instead of using SQL to do the test on the "period" parameter you do it with JS and after that append the rest of the WHERE clause to the query.</p>

micajblock

<blockquote class="ipsBlockquote" data-author="GLO_FR" data-cid="127052" data-time="1396361926"><div><p>I aggree, instead of using SQL to do the test on the "period" parameter you do it with JS and after that append the rest of the WHERE clause to the query.</p></div></blockquote><p>No need to write script in the before open. Just put the script in the default value of the parameter.</p>

GLO_FR

<p>Ok, yes</p><p>that was also my other idea (third solution

)</p><p> </p><p>like this for the month</p><pre class="_prettyXprint _lang-js">if(params["period"].value == 'last month') { BirtDateTime.month(BirtDateTime.addMonth(params["select month"].value,-1)) }else { BirtDateTime.month(params["select month"].value) }</pre><p>and this for the year</p><div><pre class="_prettyXprint _lang-js">if(params["period"].value == 'last month') { BirtDateTime.year(BirtDateTime.addMonth(params["select month"].value,-1)) }else { BirtDateTime.year(params["select month"].value)-1 }</pre><p>?</p></div>

404040

<p>@GLO_FR,<span style="font-size:14px;">@mblock</span></p><div> </div><p>Thank u very much for u r all effort ..i had learn many thing form u...and it solve all my problems</p>