Change SQL Script to show every item

Question

I have an sql script where I am trying to show customer prices for items (I modified one from Victoria Yudin):

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'

My problem is it only shows items where I have set up at least one price level. I need to to show every item from IV00101, even ones without a price level.

It would help greatly if you had a [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example). Your table names are completely meaningless; Whatever table `IV00101` is, it should be first in your `from` clause. — Stu, Aug 07 '21 at 14:22
@Stu All these are known and standard tables in Dynamics GP. If you have GP setup this is 'Reproducible'. — Randy, Aug 07 '21 at 15:13
Ah Ok and apologies, I don't use Dynamics GP, was purely looking at it from a standard SQL language perspective. You still need to join *from* your items table and `outer join` to your customers & prices. — Stu, Aug 07 '21 at 15:21

score 0 · Accepted Answer · answered Aug 07 '21 at 15:22

After playing around a bit...

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'

Change SQL Script to show every item

1 Answers1