October 5, 2021 at 7:27 pm
I have such a difficulty.
I want every record every record in WHSIBLM where IBONHD is not 0. But I only want to limit the data in the WHSDMQT table to only bring in records where the mdmovt ='SKR'. I'm afraid my where statement is limiting the WHSIBLM data to only those items that have a record in WHSDMQT.
Does someone understand what I do wrong?
SELECT w.IBWHS# AS 'DC', w.IBITM# AS 'Item Number', w2.SLZONE As 'Location Zone', w.IBHGHT AS 'Height Dimensions',
w.IBWDTH AS 'Width Dimensions', w.IBLGTH AS 'Length Dimnesions', w3.ITDES1 AS 'Title', w3.ITPTYP AS 'Publisher Type', w3.ITRPRC AS 'MSRP',
CAST(w3.ITCOST AS decimal (18,2)) AS 'WAC', r.RCLDTE AS 'Current last receipt', r2.RCLDTE AS 'Historic last receipt', w4.MDMOVT AS 'Pending kills',
s.DESDTE AS 'Last Ship Date', SUM(w.IBONHD) AS 'On hand quantity'
FROM LEVYDTA.WHSIBLM w
INNER JOIN LEVYDTA.WHSDMQT w4
ON w.IBITM#=w4.MDITM#
LEFT OUTER JOIN LEVYDTA.WHSLBLM w2
ON w.IBWHS#=w2.SLWHS# AND w.IBITM#=w2.SLITM#
LEFT OUTER JOIN LEVYDTA.WHSITMM w3
ON w.IBITM#=w3.ITITM#
LEFT OUTER JOIN LEVYDTA.RECTRNT r
ON w.IBITM#=r.RCITM#
LEFT OUTER JOIN LEVYDTA.RECTRNH r2
ON w.IBITM#=r2.RCITM#
LEFT OUTER JOIN SMPDTA.SMPDTLM s
ON w.IBITM#=s.DEITM#
WHERE w.IBONHD <>0 AND w4.MDMOVT = 'SKR' AND s.DEIO='o' AND s.DESDTE BETWEEN 20210801 AND 20210802
GROUP BY w.IBWHS#, w.IBITM#, w2.SLZONE, w.IBHGHT, w.IBWDTH, w.IBLGTH, w3.ITDES1, w3.ITPTYP, w3.ITRPRC, w3.ITCOST, r.RCLDTE, r2.RCLDTE, w4.MDMOVT,
s.DESDTE
October 5, 2021 at 9:05 pm
By putting
AND w4.MDMOVT = 'SKR'
into the WHERE clause, you effectively turned the LEFT JOIN into an inner join. Move it to the LEFT JOIN - i.e.,
LEFT OUTER JOIN LEVYDTA.WHSLBLM w2
ON w.IBITM#=w4.MDITM# AND w4.MDMOVT = 'SKR'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply