OUTER JOIN with Sub Query
When you are creating a Sub Query (i.e. getting Max EFFDT and/or Max EFFSEQ) and you are using an OUTER JOIN. Instead of creating a new view and then joining the view to your SQL statement, try placing the view’s SQL directly into the main SQL statement. This is the syntax required to accomplish this:
The example shows how to return a row from PS_VENDOR regardless if there is a corresponding record in the PS_VENDOR_ADDR table with an ADDRESS_SEQ_NUM = 3
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SELECT V.SETID, V.VENDOR_ID, V.NAME1, AD.ADDRESS1, AD.ADDRESS2, AD.ADDRESS3, AD.CITY, AD.STATE, AD.POSTAL FROM PS_VENDOR V, /* Now create a view as a table. All rows joined to PS_VOUCHER or */ /* listed in SELECT clause above must be listed in the */ /* View SELECT clause */ (SELECT SETID, VENDOR_ID, ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, POSTAL FROM PS_VENDOR_ADDR A WHERE EFFDT = (SELECT MAX(EFFDT) FROM PS_VENDOR_ADDR AA WHERE SETID = A.SETID AND VENDOR_ID = A.VENDOR_ID AND ADDRESS_SEQ_NUM = 3 AND EFFDT <= TRUNC(SYSDATE) ) AND ADDRESS_SEQ_NUM = 3 ) AD /* Alias the view just as you would a regular record */ /* Join the view with the record as normal for an outer join */ WHERE AD.SETID (+) = V.SETID AND AD.VENDOR_ID (+) = V.VENDOR_ID AND V.VENDOR_ID = 'MYVENDORNUMBER' |