ItemCount=2时CASE WHEN函数未正确返回计算结果的SQL问题

Hey, let's dig into why your CASE WHEN isn't working as expected when ItemCount equals 2!

问题背景 #

Your query is returning a Balance of 10560 for rows where you expect 5280 (which is 10560 divided by 2). The core issue is that the CASE WHEN c.ItemCount = 2 condition isn't actually being triggered—even though you think ItemCount is 2, the subquery is calculating this value incorrectly.

Root Cause #

Let's look at the subquery c that calculates ItemCount:

SELECT DISTINCT t2.docnum, t3.Dscription, t2.TransId, COUNT(t3.ItemCode) AS 'ItemCount' 
FROM OINV T2 
INNER JOIN INV1 T3 ON T2.DocEntry = T3.DocEntry 
LEFT JOIN ORIN v ON LEFT(v.NumAtCard, 6) = LEFT(t2.NumAtCard, 6) 
LEFT JOIN RCT2 s ON s.baseAbs = t2.DocEntry 
WHERE t3.Quantity > 0 AND t2.CANCELED = 'N' 
GROUP BY t2.docnum, T2.TransId, t3.Dscription, t3.ItemCode 
HAVING COUNT(t2.DocNum) = 1

The problem here is your GROUP BY includes t3.ItemCode, and COUNT(t3.ItemCode) is counting how many times that specific item appears in the group—not the total number of items for the entire document. So for each row in INV1, ItemCount is actually 1, not 2. That's why your CASE WHEN is always hitting the ELSE branch and returning the full balance.

Fixed Query #

We need to adjust the subquery to calculate the total number of items per document first, then join that back to get the correct ItemCount for each item row:

SELECT 
    c.Dscription, 
    b.BaseRef AS 'DocNum', 
    b.Ref2 AS 'NumatCard', 
    CASE WHEN c.ItemCount = 2 THEN (b.BalDueDeb - b.BalDueCred) / 2 ELSE (b.BalDueDeb - b.BalDueCred) END AS 'Balance' 
FROM OJDT a 
INNER JOIN JDT1 b ON a.TransID = b.Transid 
LEFT JOIN (
    -- First calculate total items per document, then join to item details
    SELECT 
        t2.docnum, 
        t3.Dscription, 
        t2.TransId, 
        doc_item_count.ItemCount 
    FROM OINV T2 
    INNER JOIN INV1 T3 ON T2.DocEntry = T3.DocEntry 
    -- This inner subquery gets the total number of items for each document
    INNER JOIN (
        SELECT DocEntry, COUNT(ItemCode) AS ItemCount 
        FROM INV1 
        WHERE Quantity > 0 
        GROUP BY DocEntry
    ) doc_item_count ON T2.DocEntry = doc_item_count.DocEntry 
    LEFT JOIN ORIN v ON LEFT(v.NumAtCard, 6) = LEFT(t2.NumAtCard, 6) 
    LEFT JOIN RCT2 s ON s.baseAbs = t2.DocEntry 
    WHERE t2.CANCELED = 'N' 
    GROUP BY t2.docnum, T2.TransId, t3.Dscription, doc_item_count.ItemCount 
    HAVING COUNT(t2.DocNum) = 1
) c ON a.TransId = c.TransId 
INNER JOIN OACT T5 ON T5.AcctCode = b.Account 
INNER JOIN OCRD T1 ON b.ShortName = T1.CardCode AND T1.CardType = 'C' 
WHERE T5.FORMATCODE = 11020103 
  AND (b.balduecred <> 0 OR b.balduedeb != 0) 
  AND b.BaseRef = 100078166 
GROUP BY c.Dscription, b.BaseRef, b.Ref2, b.BalDueDeb, b.BalDueCred, c.ItemCount

Key Changes #

• Added a new inner subquery doc_item_count that calculates the total number of items per DocEntry in INV1—this gives you the actual ItemCount you expect (2 for the problematic document).
• Joined this subquery back to the main subquery c so each item row gets the total item count for its document.
• Removed the unnecessary DISTINCT since the grouping already ensures unique rows.

Why This Works #

Now when ItemCount is 2 (for document 100078166), the CASE WHEN condition triggers, and (b.BalDueDeb - b.BalDueCred) gets divided by 2, returning the expected 5280 for each item row.

内容的提问来源于stack exchange，提问作者xLuca