IBM i（AS400）DB2 SQL：多分组子查询关联实现日销售报表汇总

Hey there! Let's tackle this IBM i DB2 SQL reporting problem you're having. I get it—being able to crunch individual category data but struggling to tie together those On Hold, Back Orders, and other grouped subquery results into a single daily sales report with accounting lines is super frustrating. Let's break this down step by step with a practical, IBM i-friendly solution.

CTEs are perfect here because they let you define each of your subquery results as reusable, named datasets—way cleaner than nesting subqueries in the main FROM clause. This makes it easy to manage each category's logic separately before combining them.

Let's assume your core tables are something like:

• ORDHDR: Order header (with date, status, order number)
• ORDLIN: Order line items (with amounts, item codes)
• ACCT_CODES: Mapping from items to accounting codes

Here's how to wrap each category's summary in a CTE:

WITH DailySales AS (
    SELECT 
        o.ORD_DATE,
        a.ACCT_CODE,
        SUM(ol.LINE_AMT) AS TOTAL_SALES
    FROM ORDHDR o
    JOIN ORDLIN ol ON o.ORD_NUM = ol.ORD_NUM
    JOIN ACCT_CODES a ON ol.ITEM_CODE = a.ITEM_CODE
    WHERE o.ORD_DATE = CURRENT_DATE -- Filter for today's report
    AND o.ORD_STATUS NOT IN ('H', 'B') -- Exclude hold/backorder from base sales
    GROUP BY o.ORD_DATE, a.ACCT_CODE
),
OnHoldSales AS (
    SELECT 
        o.ORD_DATE,
        a.ACCT_CODE,
        SUM(ol.LINE_AMT) AS ON_HOLD_AMT
    FROM ORDHDR o
    JOIN ORDLIN ol ON o.ORD_NUM = ol.ORD_NUM
    JOIN ACCT_CODES a ON ol.ITEM_CODE = a.ITEM_CODE
    WHERE o.ORD_DATE = CURRENT_DATE
    AND o.ORD_STATUS = 'H' -- Target On Hold status
    GROUP BY o.ORD_DATE, a.ACCT_CODE
),
BackOrderSales AS (
    SELECT 
        o.ORD_DATE,
        a.ACCT_CODE,
        SUM(ol.LINE_AMT) AS BACKORDER_AMT
    FROM ORDHDR o
    JOIN ORDLIN ol ON o.ORD_NUM = ol.ORD_NUM
    JOIN ACCT_CODES a ON ol.ITEM_CODE = a.ITEM_CODE
    WHERE o.ORD_DATE = CURRENT_DATE
    AND o.ORD_STATUS = 'B' -- Target Back Order status
    GROUP BY o.ORD_DATE, a.ACCT_CODE
)

Now, you can join these CTEs together using the common grouping columns—ORD_DATE and ACCT_CODE—to get all your metrics in one row per accounting code. Use LEFT JOIN (or FULL OUTER JOIN for edge cases) to ensure you don't lose rows where a category has no data (e.g., no On Hold orders for a specific account today).

SELECT 
    COALESCE(d.ORD_DATE, h.ORD_DATE, b.ORD_DATE) AS REPORT_DATE,
    COALESCE(d.ACCT_CODE, h.ACCT_CODE, b.ACCT_CODE) AS ACCOUNT_CODE,
    COALESCE(d.TOTAL_SALES, 0) AS TOTAL_SALES,
    COALESCE(h.ON_HOLD_AMT, 0) AS ON_HOLD_AMT,
    COALESCE(b.BACKORDER_AMT, 0) AS BACKORDER_AMT,
    -- Add calculated metrics as needed
    COALESCE(d.TOTAL_SALES, 0) + COALESCE(h.ON_HOLD_AMT, 0) + COALESCE(b.BACKORDER_AMT, 0) AS GRAND_TOTAL
FROM DailySales d
FULL OUTER JOIN OnHoldSales h 
    ON d.ORD_DATE = h.ORD_DATE 
    AND d.ACCT_CODE = h.ACCT_CODE
FULL OUTER JOIN BackOrderSales b 
    ON COALESCE(d.ORD_DATE, h.ORD_DATE) = b.ORD_DATE 
    AND COALESCE(d.ACCT_CODE, h.ACCT_CODE) = b.ACCT_CODE
ORDER BY ACCOUNT_CODE;

• COALESCE is non-negotiable: It replaces NULL values (when a category has no data for an account) with 0, making your report clean and consistent.
• FULL OUTER JOIN safety: This ensures you capture accounts that only have On Hold/Back Order data (but no regular sales) and vice versa. If you know all accounts will have at least one entry, you can switch to LEFT JOIN instead.
• Date handling: IBM i DB2 supports CURRENT_DATE for today's date, or DATE(SYSDATE) if your system uses that format. Adjust the filter to match your table's date column.
• Index optimization: If your tables are large, add indexes on join columns like ORD_NUM, ORD_DATE, ACCT_CODE, and ITEM_CODE—this will speed up the query significantly on IBM i.

• Mismatched grouping columns: Double-check that every CTE groups by the exact same columns (e.g., don't forget ORD_DATE in one CTE). Mismatches will break the join alignment.
• Status code discrepancies: Make sure your status values ('H' for On Hold, 'B' for Back Order) match what's actually stored in your ORDHDR table.
• Null account codes: If some items don't map to an accounting code, add a COALESCE(a.ACCT_CODE, 'UNKNOWN') to avoid losing those rows.

If you can share your existing subqueries or table structures, I can tweak this example to match your exact setup. But this pattern should work for most daily sales reporting scenarios on IBM i DB2.

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