如何在 SQL 查詢中重寫 IFNULL 或 COALESCE？

我有一種方法可以重寫 SQL 查詢，所以我可以避免IFNULLor COALESCE，因為 HXTT DBF JDBC 驅動程序（支持 SQL92）會拋出以下錯誤：

Caused by: java.sql.SQLException: The aggregate function SUM(NETTO) can only be used in select-list, HAVING clause, and ORDER BY clause of SELECT statement.

以下查詢導致上述錯誤：

SELECT
(   SELECT IFNULL (SUM (shipManagementInvoice.netto ), 0) AS shipManagementInvoiceNetValue
   FROM tckopf AS shipManagementInvoice
   WHERE shipManagementInvoice.referenzid = 1
       AND shipManagementInvoice.btyp = 5 )
-
(   SELECT IFNULL (SUM (shipManagementCreditNote.netto), 0) AS shipManagementCreditNoteNetValue
   FROM tckopf AS shipManagementCreditNote
   WHERE shipManagementCreditNote.referenzid = 1
       AND shipManagementCreditNote.btyp = 6 )
AS shipManagementResult

如果我刪除IFNULLor COALESCE，則沒有錯誤。

有幾種方法可以重寫查詢，甚至更多，因為兩個子查詢都使用相同的基表。不知道為什麼會拋出錯誤以及應該歸咎於誰，JDBC驅動程序，Foxpro或其他東西，所以這裡有一些替代方案：

• (1) 對兩個搜尋使用一個查詢：

SELECT COALESCE(SUM(CASE WHEN t.btyp = 5 THEN t.netto
                        WHEN t.btyp = 6 THEN - t.netto 
                                        ELSE 0 
                   END), 0) AS shipManagementResult
FROM tckopf AS t
WHERE t.referenzid = 1 ;

• (2) 在外部級別中拉取COALESCE()orIFNULL()函式呼叫，或者使用原始查詢：

SELECT 
 COALESCE( ( SELECT SUM(i.netto) 
             FROM tckopf AS i
             WHERE i.referenzid = 1
               AND i.btyp = 5 
           ), 0)
- COALESCE( ( SELECT SUM(c.netto) 
             FROM tckopf AS c
             WHERE c.referenzid = 1
               AND c.btyp = 6 
           ), 0)
 AS shipManagementResult ;

• (2b) 或上述 (1st) 查詢：

SELECT COALESCE( 
 ( SELECT SUM(CASE WHEN t.btyp = 5 THEN t.netto
                   WHEN t.btyp = 6 THEN - t.netto 
                                   ELSE 0 
               END)
   FROM tckopf AS t
   WHERE t.referenzid = 1
 ), 0) AS shipManagementResult ;

引用自：https://dba.stackexchange.com/questions/58841