Using Outer Joins AND Effective Dated Logic

How can you use outer joins on a table and still use effective dated logic? Something like this.

Note that the outer join will either return no rows, and thus a NULL value for EFFDT, or it will return a date and subsequent effective dated logic can be used.

AND C.BUSINESS_UNIT (+) = A.BUSINESS_UNIT
AND C.COMMON_ID (+) = A.COMMON_ID
AND C.SA_ID_TYPE (+) = A.SA_ID_TYPE
AND C.ITEM_NBR_CHARGE (+) = A.ITEM_NBR
AND
(NULL = C.EFFDT
OR
(C.EFFDT =
(SELECT MAX(C_ED.EFFDT)
FROM sysadm.ps_ITEM_XREF C_ED
WHERE C_ED.BUSINESS_UNIT = C.BUSINESS_UNIT
AND C_ED.COMMON_ID = C.COMMON_ID
AND C_ED.SA_ID_TYPE = C.SA_ID_TYPE
AND C_ED.ITEM_NBR_CHARGE = C.ITEM_NBR_CHARGE
AND C_ED.ITEM_NBR_PAYMENT = C.ITEM_NBR_PAYMENT
AND C_ED.EFFDT <= SYSDATE)))

0 comments: