postgresql - SQL 查询在 pentaho 报表设计器中不工作，而相同的查询在 postgresql 中工作正常

Question

SQL 查询在 pentaho 报表设计器中不工作，而相同的查询在 postgresql 中工作正常。不知道为什么？

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

当我尝试在 pentaho 报表设计器中运行它时，出现的错误是:

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

Answer 1

试试这个查询，

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