尽管存在COALESCE，但从SQL结果中排除的项目

德马修

我试图提取自@StartDate以来客户购买的所有商品ID。自零件出售以来，某些Item ID已有更改，因此我在SELECT语句中添加了COALESCE，希望它可以返回零件的新Item ID，但是唯一要返回的零件是具有备用代码的零件。从未更改且没有替代代码的项目ID不在结果中……我不确定为什么。

SELECT DISTINCT p21_view_invoice_line.supplier_id 
                AS 
                [Supp ID], 
                address_1.NAME 
                AS Supplier, 
                address.NAME 
                AS Customer, 
                COALESCE(p21_view_alternate_code.item_id, 
                p21_view_invoice_line.item_id) AS 
                [Item ID], 
                p21_view_alternate_code.item_desc 
                AS [Item Desc], 
                p21_view_invoice_line.customer_part_number 
                AS CPN, 
                p21_view_invoice_line.unit_of_measure 
                AS UOM, 
                p21_view_invoice_hdr.order_no 
                AS [Order No], 
                p21_view_invoice_hdr.invoice_date 
                AS [Invoice Date], 
                p21_view_invoice_line.qty_shipped 
                AS [Qty Shipped], 
                p21_view_invoice_line.unit_price 
                AS [Last Invoiced Sales Price], 
                p21_view_invoice_line.commission_cost 
                AS [Last Invoice Comm Cost], 
                inv_loc.standard_cost 
                AS [Current Standard Cost] 
FROM   p21_view_invoice_hdr 
       INNER JOIN p21_view_invoice_line 
               ON p21_view_invoice_hdr.invoice_no = 
                  p21_view_invoice_line.invoice_no 
       INNER JOIN inv_loc 
               ON p21_view_invoice_line.company_id = inv_loc.company_id 
       INNER JOIN address AS address_1 
               ON p21_view_invoice_line.supplier_id = address_1.id 
       INNER JOIN address 
               ON p21_view_invoice_hdr.customer_id = address.id 
       INNER JOIN p21_view_alternate_code 
               ON inv_loc.inv_mast_uid = p21_view_alternate_code.inv_mast_uid 
                  AND p21_view_invoice_line.inv_mast_uid = 
                      p21_view_alternate_code.inv_mast_uid 
WHERE  ( p21_view_invoice_hdr.customer_id = @Customer_ID ) 
       AND ( p21_view_invoice_hdr.invoice_date >= @StartDate ) 
ORDER  BY [supp id], 
          [item id], 
          [invoice date] DESC, 
          [current standard cost] DESC

德马修

我重新编写了代码，并为两个Item ID标识符列inv_mas_uid包括了FULL OUTER JOIN，这解决了问题。查询大约需要2-3秒。

这是新的查询。

SELECT DISTINCT 
p21_view_invoice_line.supplier_id AS [Supp ID]
, address_1.name AS Supplier
, p21_view_invoice_hdr.customer_id AS [Customer ID]
, address.name AS Customer
, COALESCE (p21_view_alternate_code.item_id, p21_view_invoice_line.item_id) AS [Item ID]
, COALESCE (p21_view_alternate_code.item_desc, p21_view_invoice_line.item_desc) AS [Item Desc]
--, p21_view_invoice_line.inv_mast_uid
, p21_view_invoice_line.customer_part_number AS CPN
, p21_view_invoice_hdr.order_no AS [Order No]
, p21_view_invoice_hdr.invoice_date AS [Invoice Date]
, p21_view_invoice_line.qty_shipped AS [Qty Shipped]
, p21_view_invoice_line.unit_of_measure AS UOM
, p21_view_invoice_line.commission_cost AS [Comm Cost]
, p21_view_invoice_line.unit_price AS [Unit Price]
, inv_loc.standard_cost AS [Current Std Cost]

FROM            
address 
INNER JOIN p21_view_alternate_code 
FULL OUTER JOIN p21_view_invoice_line ON p21_view_alternate_code.inv_mast_uid = p21_view_invoice_line.inv_mast_uid 
INNER JOIN p21_view_invoice_hdr ON p21_view_invoice_line.invoice_no = p21_view_invoice_hdr.invoice_no 
INNER JOIN inv_loc ON p21_view_invoice_line.inv_mast_uid = inv_loc.inv_mast_uid 
INNER JOIN address AS address_1 ON p21_view_invoice_line.supplier_id = address_1.id ON address.id = p21_view_invoice_hdr.customer_id

WHERE     
(p21_view_invoice_hdr.customer_id = @Customer_ID)    
AND (p21_view_invoice_hdr.invoice_date >= @Begin_Date) 
--AND (p21_view_invoice_line.supplier_id = 9219) 
AND (p21_view_invoice_line.supplier_id IS NOT NULL)

ORDER BY 
[Supplier]
, [Invoice Date] DESC
, [Item ID]

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。