“列名无效"的解决方法 [英] Workaround with 'Column Name is not valid'

问题描述

这个有什么解决方法吗?

Hi is there any workaround with this one?

SELECT        invoices.no, customers.name, invoices.mdcode,
SUM(iproducts.unitprice * iproducts.quantity) AS total,
SUM(iproducts.unitprice * iproducts.quantity) - SUM(rinvoices.payment + rinvoices.discount)
AS [Remaining Balance]
FROM          invoices INNER JOIN
              customers ON customers.id = invoices.customerid INNER JOIN
              iproducts ON invoices.id = iproducts.invoiceid LEFT OUTER JOIN
              rinvoices ON invoices.id = rinvoices.invoiceid
WHERE        ([Remaining Balance] <> '0')
GROUP BY invoices.no, customers.name, invoices.mdcode

以下返回一个错误，即[剩余余额]"不是有效列，而我已经声明了它.我不能在 WHERE 和 CASE 子句中使用它.是否有任何解决办法，因为我真的只需要获取仍有余额的发票.

The following returns an error that '[Remaining Balance]' is not a valid column whereas i already declared it. i can't use it in both the WHERE and the CASE clauses. Is there any work around since i really need to get only the invoices that have still remaining balances.

感谢您的帮助！

推荐答案

你必须像这样使用 HAVING 子句

You have to use HAVING clause like this

HAVING SUM(rinvoices.payment) - SUM(rinvoices.discount) <> 0

而不是WHERE

即:

SELECT        invoices.no, customers.name, invoices.mdcode,
SUM(iproducts.unitprice * iproducts.quantity) AS total,
SUM(iproducts.unitprice * iproducts.quantity) - SUM(rinvoices.payment + rinvoices.discount)
AS [Remaining Balance]
FROM          invoices INNER JOIN
              customers ON customers.id = invoices.customerid INNER JOIN
              iproducts ON invoices.id = iproducts.invoiceid LEFT OUTER JOIN
              rinvoices ON invoices.id = rinvoices.invoiceid
GROUP BY invoices.no, customers.name, invoices.mdcode
HAVING SUM(rinvoices.payment) - SUM(rinvoices.discount) <> 0

这篇关于“列名无效"的解决方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！