October 31, 2008 at 12:10 pm
Hi 😀 and thanks in advance for any help.
Fairly new to the power of 'real' SQL, and i have a question regarding conditional joins...
Please could someone (if they can understand it) let me know if the following code would only return all orders from tbl_BPMPromiseDates where the PromiseDate field in there is less than the date in tbl_BPMCutOffDate ?
UPDATE tbl_BPMHDR
SET CriticalID = 2, CriticalPrmLvl = tbl_BPMPromiseDates.PrmLvlID
FROM vw_BPMCurPrmLvl INNER JOIN
tbl_BPMPromiseDates ON vw_BPMCurPrmLvl.OrderNum = tbl_BPMPromiseDates.OrderNum AND
vw_BPMCurPrmLvl.CurrentPrmLvl = tbl_BPMPromiseDates.PrmLvlID INNER JOIN
tbl_BPMHDR ON tbl_BPMPromiseDates.OrderNum = tbl_BPMHDR.OrderNum INNER JOIN
tbl_BPMCutOffDate ON tbl_BPMPromiseDates.PromiseDate < tbl_BPMCutOffDate.CutOffDate LEFT OUTER JOIN
(SELECT tbl_BPMStockDelayOrders.OrderNum
FROM tbl_BPMStockDelayOrders INNER JOIN
tbl_BPMStockDelays ON tbl_BPMStockDelayOrders.Component = tbl_BPMStockDelays.Component AND
tbl_BPMStockDelayOrders.WHID = tbl_BPMStockDelays.WHID
GROUP BY tbl_BPMStockDelayOrders.OrderNum, tbl_BPMStockDelays.bCutOffDate
HAVING (tbl_BPMStockDelays.bCutOffDate = 1)) AS dtbl_CutOff ON tbl_BPMPromiseDates.OrderNum = dtbl_CutOff.OrderNum
WHERE (tbl_BPMPromiseDates.StockDelay = 1) AND (tbl_BPMHDR.CriticalID <> 2) AND (dtbl_CutOff.OrderNum IS NOT NULL)
October 31, 2008 at 1:17 pm
This should only update rows in tbl_BPMHDR that have a promise date < ANY cutoff date. Are you sure you don't have a part of the join missing? If you have more than 1 row in tbl_BPMCutOffDate you could be updating more than you bargain for. Also you could eliminate the LEFT JOIN because you are the dtbl_Cutoff.OrderNum IS NOT NULL turns that into an INNER JOIN any way.
Without seeing the schemas there are no guarantees in what my recommendations.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 31, 2008 at 3:36 pm
That's great, many thanks. tbl_BPMCutOffDate only ever has one date, also thanks for spotting the tidy up on the outer join.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply