在iReport中的SQL subSelect Statement问题 [英] SQL subSelect Statement issue in iReport

问题描述

我正在使用iReport编写自定义报告并遇到问题。

I am using iReport to write custom reports and have ran into an issue.

我试图通过添加以下代码行来修改现有报告：

I am attempting to alter an existing report by adding the line of code that is:

LEFT JOIN (select info from customfieldview where cftableid = 97022306 and recordid = so.id and cfname = 'SIN')

字段和表名都是正确的但无论出于何种原因它都说我的WHERE语句是混乱的起来。对于iReport，它的调试器通常会说问题是实际问题下面的行，而我的WHERE语句之前的行恰好是我的subSELECT语句。

The field and table names are all correct but for whatever reason it is saying that my "WHERE" statement is messed up. Which with iReport, it's debugger usually says the issue is the line below the actual problem and the line before my WHERE statement just happens to be my subSELECT statement.

我将发布我的SQL和照片突出了我的问题。

I will post my SQL and a photo highlighting my issue.

我的subSelect语句格式不正确吗？

Is my subSelect statement formatted incorrectly?

SELECT (case when $P{ckShowHistoricalProductNumber} = 1 then soitem.productnum else product.num end) AS soitemproductnum, soitem.description AS soitemdescription,
(case when soitem.uomid != product.uomid then ((postsoitem.qty*uomconversion.multiply)/uomconversion.factor) else postsoitem.qty end) AS postsoitemqty,
producttree.name AS producttreename,
(CASE WHEN soitem.uomid != product.uomid then (soitem.unitprice/uomconversion.multiply)*uomconversion.factor else soitem.unitprice end) + (soitem.adjustamount / (CASE WHEN soitem.qtytofulfill = 0
                                                        THEN 1
                                                        ELSE soitem.qtytofulfill END)) AS soitemunitprice, soitem.typeid AS soitemtypeid,
company.name AS company, uom.code AS uomcode, currency.symbol

FROM soitem
LEFT JOIN postsoitem ON soitem.id = postsoitem.soitemid
JOIN postso on postsoitem.postsoid = postso.id
JOIN product ON soitem.productid = product.id
LEFT JOIN producttotree ON product.id = producttotree.productid
LEFT JOIN producttree ON producttotree.producttreeid = producttree.id
LEFT JOIN uom ON product.uomid = uom.id
LEFT JOIN uomconversion on product.uomid = uomconversion.touomid and soitem.uomid = uomconversion.fromuomid
INNER JOIN company ON company.id = 1
LEFT JOIN currency ON currency.homeCurrency = 1
LEFT JOIN (select info from customfieldview where cftableid = 97022306 and recordid = so.id and cfname = 'SIN')

WHERE postso.postdate BETWEEN $P{dateRange1} AND $P{dateRange2}
AND soitem.typeid IN (10,11,12,21,30,31,40,50,60,70,80)
AND ((COALESCE(producttreeid,0) IN ($P!{productTree1})) OR ((COALESCE(producttreeid,0) LIKE $P{productTree2})))
AND (CASE WHEN $P!{ckShowEachProductOnce} =1 then (Select first 1 producttree.id
       FROM product AS p1
       LEFT JOIN producttotree ON p1.id = producttotree.productid
       LEFT JOIN producttree ON producttotree.producttreeid = producttree.id
       WHERE p1.id = product.id
       AND ((COALESCE(producttreeid,0) IN ($P!{productTree1})) OR ((COALESCE(producttreeid,0) LIKE $P{productTree2})))) else producttree.id end) = producttree.id

推荐答案

事实证明问题是我编写子查询的方式。

It turns out that the issue was the way I was programming the subQuery.

我是使用 LEFT JOIN（从customfieldview中选择信息，其中cftableid = 97022306和recordid = so.id和cfname ='SIN'）

并说这是一个 subQuery ，实际上因为它是在 FROM 条件/争论中使用的，那么它实际上是一个派生表。

and saying it was a subQuery when in fact because it was being use in the FROM condition/arguement then it is actually a Derived Table.

我不知道有什么区别！

谢谢你们输入和有用的提示！

Thank you guys for the input and helpful tips!

这篇关于在iReport中的SQL subSelect Statement问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！