Best type of join to use

  Kiến thức lập trình

I’m having an issue with the below query with either filtering out rows that are needed, or including extra rows that aren’t desired (Depending on whether or not I use the OR condition below). I am LEFT OUTER JOINing PO_ATTRIBUTE_VALUES as their are some cases where there are some rows in PO_ATTRIBUTE_VALUES that are not in PO_LINES_ARCHIVE_ALL. The issue is in the final WHERE clause where I only return rows from PO_LINES_ARCHIVE_ALL LN with a LAST_UPDATE_DATE within the last two days, OR where the LAST_UPDATE_DATE from PO_ATTRIBUTE_VALUES is within the last two days. If I don’t use the ‘OR` condition then I am filtering out rows that are needed that are in the Outer joined table.

The issue then becomes when I use the OR criteria then the results are returning back additional rows from PO_ATTRIBUTE_VALUES that aren’t joined to PO_LINES_ARCHIVE_ALL (because of the LEFT OUTER JOIN and within the date range). How can I handle this situation?

(SELECT 'LINE_UPDATE' UPDATE_TYPE, LN.PO_HEADER_ID , LN.PO_LINE_ID , LN.LAST_UPDATE_DATE, LN.UNIT_PRICE, LN.VENDOR_PRODUCT_NUM, LN.UOM_CODE, ATTR.MANUFACTURER_PART_NUM  , TLP.MANUFACTURER
 , LN_OLD.LAST_UPDATE_DATE LN_OLD_LAST_UPDATE_DT, ETP.TP_ITEM_NUMBER OLD_MFG_PART,    LN_OLD.MANUFACTURER OLD_MFG     
 , LN_OLD.VENDOR_PRODUCT_NUM OLD_VENDOR_ITEM, 
 LN_OLD.UNIT_PRICE OLD_UNIT_PRICE, LN_OLD.UOM_CODE OLD_UOM_CODE, LN.ITEM_ID, ATTR.CREATION_DATE MFG_CREATION_DATE, ATTR.LAST_UPDATE_DATE MFG_LAST_UPDATE_DATE
FROM PO_LINES_ARCHIVE_ALL LN

LEFT OUTER JOIN EGP_SYSTEM_ITEMS ITM ON ITM.INVENTORY_ITEM_ID = LN.ITEM_ID AND ITM.ORGANIZATION_ID = 300000010136542 --Master Org.
LEFT OUTER JOIN EGP_ITEM_RELATIONSHIPS_B EIR ON EIR.INVENTORY_ITEM_ID = LN.ITEM_ID
                  AND EIR.ITEM_RELATIONSHIP_TYPE = 'MFG_PART_NUM'
LEFT OUTER JOIN EGP_TRADING_PARTNER_ITEMS ETP ON ETP.TP_ITEM_ID = EIR.TP_ITEM_ID
                  AND ETP.TP_TYPE = 'MANUFACTURER'
LEFT OUTER JOIN PO_ATTRIBUTE_VALUES ATTR ON ATTR.PO_HEADER_ID = LN.PO_HEADER_ID AND ATTR.PO_LINE_ID = LN.PO_LINE_ID   AND ATTR.LAST_UPDATE_DATE > SYSDATE - 22 AND ATTR.MANUFACTURER_PART_NUM <> ETP.TP_ITEM_NUMBER
LEFT OUTER JOIN PO_ATTRIBUTE_VALUES_TLP TLP ON TLP.PO_HEADER_ID = LN.PO_HEADER_ID AND TLP.PO_LINE_ID = LN.PO_LINE_ID  AND TLP.LAST_UPDATE_DATE > SYSDATE - 22

LEFT OUTER JOIN PO_LINES_ARCHIVE_ALL LN_OLD ON LN_OLD.PO_HEADER_ID = LN.PO_HEADER_ID AND LN_OLD.PO_LINE_ID = LN.PO_LINE_ID                      

WHERE (LN.LAST_UPDATE_DATE > SYSDATE - 2 )) --OR ATTR.LAST_UPDATE_DATE > SYSDATE - 2))

LEAVE A COMMENT