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
source to share
2 answers
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
+1
source to share
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());
0
source to share