更改SQL脚本以显示每个项目

Question

我有一个sql脚本，我试图显示客户价格的项目(我修改了一个从维多利亚尤丁):

SELECT
RTRIM(LTRIM(IV.ITEMNMBR)) AS ItemNumber
,RTRIM(LTRIM(IM.ITEMDESC)) AS ItemDescription   
,CASE IM.PRICMTHD
WHEN 1 THEN IV.UOMPRICE
WHEN 2 THEN IV.UOMPRICE * IC.LISTPRCE / 100
WHEN 3 THEN (IM.CURRCOST) * (1 + (IV.UOMPRICE / 100))
WHEN 4 THEN (IM.STNDCOST) * (1 + (IV.UOMPRICE / 100))
WHEN 5 THEN (IM.CURRCOST) / (1 - (IV.UOMPRICE / 100))
WHEN 6 THEN (IM.STNDCOST) / (1 - (IV.UOMPRICE / 100))
ELSE 0
END AS Price
,IQ.QTYONHND AS QtyOnHand   
,C.CUSTNMBR AS CustomerNumber
FROM dbo.RM00101 AS C
LEFT OUTER JOIN dbo.IV00108 AS IV
ON (CASE
WHEN C.PRCLEVEL IS NULL THEN 'RETAIL'
ELSE C.PRCLEVEL
END) = IV.PRCLEVEL
LEFT OUTER JOIN dbo.IV00101 AS IM
ON IM.ITEMNMBR = IV.ITEMNMBR
LEFT OUTER JOIN dbo.IV00102 AS IQ
ON IQ.ITEMNMBR = IV.ITEMNMBR
AND IQ.RCRDTYPE = 1
LEFT OUTER JOIN dbo.IV00105 AS IC
ON IC.ITEMNMBR = IV.ITEMNMBR
AND IV.CURNCYID = IC.CURNCYID
WHERE C.CUSTNMBR = 'SomeCustomer001'

我的问题是它只显示我设置了至少一个价格水平的项目。我需要展示IV00101的每一件商品，甚至那些没有价格等级的。

Answer 1

在玩了一会儿之后…

SELECT
RTRIM(LTRIM(IM.ITEMNMBR)) AS ItemNumber
,RTRIM(LTRIM(IM.ITEMDESC)) AS ItemDescription
,CASE IM.PRICMTHD
WHEN 1 THEN IV.UOMPRICE
WHEN 2 THEN IV.UOMPRICE * IC.LISTPRCE / 100
WHEN 3 THEN (IM.CURRCOST) * (1 + (IV.UOMPRICE / 100))
WHEN 4 THEN (IM.STNDCOST) * (1 + (IV.UOMPRICE / 100))
WHEN 5 THEN (IM.CURRCOST) / (1 - (IV.UOMPRICE / 100))
WHEN 6 THEN (IM.STNDCOST) / (1 - (IV.UOMPRICE / 100))
ELSE 0
END AS Price
,IQ.QTYONHND AS QtyOnHand
,C.CUSTNMBR AS CustomerNumber
FROM dbo.IV00101 AS IM
LEFT OUTER JOIN dbo.RM00101 AS C
ON c.INACTIVE = 0
LEFT OUTER JOIN dbo.IV00108 AS IV
ON (CASE
WHEN C.PRCLEVEL IS NULL THEN 'RETAIL'
ELSE C.PRCLEVEL
END) = IV.PRCLEVEL
AND IM.ITEMNMBR = IV.ITEMNMBR
LEFT OUTER JOIN dbo.IV00102 AS IQ
ON IQ.ITEMNMBR = IV.ITEMNMBR
AND IQ.RCRDTYPE = 1
LEFT OUTER JOIN dbo.IV00105 AS IC
ON IC.ITEMNMBR = IV.ITEMNMBR
AND IV.CURNCYID = IC.CURNCYID
WHERE C.CUSTNMBR = 'SomeCustomer001'