Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Parameterize limit

  1. #1

    Default Parameterize limit

    I've tried many different ways to parameterize this but PRD is always returning "Failed at query... line xx is null". I suspect it is not parsing the limit number through.
    We've got a report with a detail section that yields out pages and pages of data rows sorted by highest number of counts. Essentially it'd make more sense to show only top 10, which can be hard-coded as SELECT...
    ...
    ORDER BY COUNT DESC
    LIMIT 10

    But we want to leave the limit as a parameter so that end users can choose how many they want to see, eg. top 100, or top 10,000 (show all).
    Can this be done in PRD? Any help/hints appreciated.

    Thanks!

  2. #2
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    PRD will spit out a stacktrace on every error (red symbol in the status bar). Gimme the full stacktrace and I tell you what goes wrong.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  3. #3

    Default

    org.pentaho.reporting.engine.classic.core.ReportDataFactoryException: Failed at query: /*Detailed Records*/
    SELECT
    blah
    , blahblah

    FROM temp_table t
    JOIN DIM_A a on a.col1 = cast(t.col8 as char) and a.latest=true
    LEFT OUTER JOIN (
    ) x ON x.recipient = s.msisdn

    WHERE com=${comlist}
    GROUP BY col1
    ORDER BY x.bla DESC
    LIMIT ${setlimit} //<-- COMMENT:: this is line 37


    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:254)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:95)
    at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryData(CompoundDataFactory.java:90)
    at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryInternal(CachingDataFactory.java:249)
    at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryData(CachingDataFactory.java:206)
    at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryData(CompoundDataFactory.java:99)
    at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryData(CompoundDataFactory.java:99)
    at org.pentaho.reporting.engine.classic.core.states.datarow.ReportDataRow.createDataRow(ReportDataRow.java:97)
    at org.pentaho.reporting.engine.classic.core.states.datarow.DefaultFlowController.performSubReportQuery(DefaultFlowController.java:260)
    at org.pentaho.reporting.engine.classic.core.states.process.ProcessState.initializeForSubreport(ProcessState.java:444)
    at org.pentaho.reporting.engine.classic.core.states.process.EndSubReportHandler.commit(EndSubReportHandler.java:59)
    at org.pentaho.reporting.engine.classic.core.states.process.ProcessState.commit(ProcessState.java:831)
    at org.pentaho.reporting.engine.classic.core.layout.output.AbstractReportProcessor.processPrepareLevels(AbstractReportProcessor.java:427)
    at org.pentaho.reporting.engine.classic.core.layout.output.AbstractReportProcessor.performStructuralPreprocessing(AbstractReportProcessor.java:594)
    at org.pentaho.reporting.engine.classic.core.layout.output.AbstractReportProcessor.prepareReportProcessing(AbstractReportProcessor.java:496)
    at org.pentaho.reporting.engine.classic.core.modules.output.pageable.graphics.PrintReportProcessor.getNumberOfPages(PrintReportProcessor.java:79)
    at org.pentaho.reporting.engine.classic.core.modules.gui.base.PreviewPane$RepaginationRunnable.run(PreviewPane.java:273)
    at org.pentaho.reporting.engine.classic.core.util.Worker.run(Worker.java:174)
    ParentException:
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null' at line 37
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.Util.getInstance(Util.java:381)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2228)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.parametrizeAndQuery(SimpleSQLReportDataFactory.java:412)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:250)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:95)
    at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryData(CompoundDataFactory.java:90)
    at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryInternal(CachingDataFactory.java:249)
    at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryData(CachingDataFactory.java:206)
    at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryData(CompoundDataFactory.java:99)
    at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryData(CompoundDataFactory.java:99)
    at org.pentaho.reporting.engine.classic.core.states.datarow.ReportDataRow.createDataRow(ReportDataRow.java:97)
    at org.pentaho.reporting.engine.classic.core.states.datarow.DefaultFlowController.performSubReportQuery(DefaultFlowController.java:260)
    at org.pentaho.reporting.engine.classic.core.states.process.ProcessState.initializeForSubreport(ProcessState.java:444)
    at org.pentaho.reporting.engine.classic.core.states.process.EndSubReportHandler.commit(EndSubReportHandler.java:59)
    at org.pentaho.reporting.engine.classic.core.states.process.ProcessState.commit(ProcessState.java:831)
    at org.pentaho.reporting.engine.classic.core.layout.output.AbstractReportProcessor.processPrepareLevels(AbstractReportProcessor.java:427)
    at org.pentaho.reporting.engine.classic.core.layout.output.AbstractReportProcessor.performStructuralPreprocessing(AbstractReportProcessor.java:594)
    at org.pentaho.reporting.engine.classic.core.layout.output.AbstractReportProcessor.prepareReportProcessing(AbstractReportProcessor.java:496)
    at org.pentaho.reporting.engine.classic.core.modules.output.pageable.graphics.PrintReportProcessor.getNumberOfPages(PrintReportProcessor.java:79)
    at org.pentaho.reporting.engine.classic.core.modules.gui.base.PreviewPane$RepaginationRunnable.run(PreviewPane.java:273)
    at org.pentaho.reporting.engine.classic.core.util.Worker.run(Worker.java:174)

  4. #4
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    I am surprised that MySQL allows you to parametrize the limit clause - the contract usually says: Only LValues can be parametrized. But then again, its MySQL.

    However, it seems that your parameter "setlimit" is not set. So did you make this parameter mandatory? do you have a default value? Is it even defined?
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

  5. #5

    Default

    The value type was integer.
    The parameter was configured to accept a default value of 10.
    The display type as Text Box.

    No, it was not made mandatory. Should I? I'll try...

    Alright. Tried mandatory. Did not work. Same error where it says "...null at near line 37"

    Any other ideas?
    Last edited by dat789; 04-20-2012 at 04:47 AM.

  6. #6
    Join Date
    Oct 2010
    Posts
    24

    Default

    I have tried it with postgresql, it worked good.
    You are better to give a real number of limit in the query and see if query works. then try parameter.

  7. #7

    Default

    I have actually limited the number of rows to return as anyone would do in a query (select ... from... limit 10 ).
    That worked without a problem.
    Point was when I parameterized the limit (... limit ${setlimit} ) it throws out an error of which you can find in the stacktrace I pasted in this thread.

    If I've misunderstood you please then correct me. Thanks!

  8. #8
    Join Date
    Mar 2003
    Posts
    8,085

    Default

    Again, it sounds as if (a) you either cannot parametrize limits in MySQL or (b) you can theoretically, but the MySQL driver is buggy and just does not do it. In either way you are out of options with MySQL.

    You can however use the "limit" attribute on the report with a suitable formula, if the query happens to be a main query for either the master or a sub-report.
    Get the latest news and tips and tricks for Pentaho Reporting at the Pentaho Reporting Blog.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.