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
varun chauhan
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
Working Hard ..
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
julio lima
source
to share