CODEWITHSUNDEEP
postgresqlpentaho
varun chauhan
varun chauhan

Reputation: 31

SQL query isnt working in pentaho report designer whereas the same query is working fine in postgresql

SQL query isnt working in pentaho report designer whereas the same query is working fine in postgresql . Couldnt 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 , error comes is :

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

Upvotes: 3

Views: 365

Answers (2)

julio lima
julio lima

Reputation: 1

I had the same problem when running in 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());

Upvotes: 0

Helping Hand..
Helping Hand..

Reputation: 2440

try out 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

Upvotes: 1

Related Questions