SQL query doesn't work in pentaho report designer whereas the same query works fine in postgresql

Question

SQL query doesn't work in pentaho report designer whereas the same query works fine in postgresql

SQL query does not work in pentaho report designer, whereas the same query works fine in postgresql. Couldn't figure out why?

SELECT 
  product_product.default_code AS Sku,
  stock_inventory_line.product_qty AS Quantity
FROM 
  public.product_product, 
  public.product_template, 
  public.product_category, 
  public.stock_inventory_line
WHERE 
  product_product.product_tmpl_id = product_template.id AND
  product_template.categ_id = product_category.id AND
  stock_inventory_line.product_id = product_product.id AND
  product_category.name = 'Bboy'
 ORDER BY
  product_product.default_code ASC

when i try to run this in pentaho report designer the error occurs:

at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:214)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:162)
    at org.pentaho.reporting.ui.datasources.jdbc.ui.JdbcPreviewWorker.run(JdbcPreviewWorker.java:95)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.NullPointerException
    at org.postgresql.core.v3.SimpleParameterList.setNull(SimpleParameterList.java:137)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.setNull(AbstractJdbc2Statement.java:1215)
    at org.postgresql.jdbc3.AbstractJdbc3Statement.setNull(AbstractJdbc3Statement.java:1490)
    at org.postgresql.jdbc4.AbstractJdbc4Statement.setNull(AbstractJdbc4Statement.java:84)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1874)
    at org.postgresql.jdbc3g.AbstractJdbc3gStatement.setObject(AbstractJdbc3gStatement.java:36)
    at org.postgresql.jdbc4.AbstractJdbc4Statement.setObject(AbstractJdbc4Statement.java:47)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.parametrize(SimpleSQLReportDataFactory.java:418)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.parametrizeAndQuery(SimpleSQLReportDataFactory.java:326)
    at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:209)
    ... 3 more

+3

postgresql pentaho

varun chauhan 03 jul. At 7:09 am

source to share

2 answers

Working Hard .. · Answer 1 · 2015-07-03T09:03:02+0000

try this query,

SELECT 
  product_product.default_code AS Sku,
  stock_inventory_line.product_qty AS Quantity
FROM 
  public.product_product, 
  public.product_template, 
  public.product_category, 
  public.stock_inventory_line
WHERE 
  product_product.product_tmpl_id = product_template.id AND
  product_template.categ_id = product_category.id AND
  stock_inventory_line.product_id = product_product.id AND
  product_category.name = 'Bboy'
 ORDER BY
  1 ASC

julio lima · Answer 2 · 2017-06-13T18:12:36+0000

I had the same problem while running in a Java application and the code below works for me:

myReport.getDataFactory().initialize(new DesignTimeDataFactoryContext(myReport));
// your code...
TableModel myTable = myReport.getDataFactory().queryData(queryName, new ParameterDataRow());

SQL query doesn't work in pentaho report designer whereas the same query works fine in postgresql

More articles: