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