Discussions
Categories
Choose a Product
THRUST SERVICES
CORE APPS
CE PRODUCTS
...
Quick Links
POPULAR
HELPFUL TIPS
Groups
My Links
FOR SIGNED IN MEMBERS:
Back to website
Home
Designing Analytics Reports
oracle date parameter
Migrateduser
<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
There are no comments yet