5 Replies Latest reply: Mar 25, 2014 5:57 AM by Phil Colbert RSS

    Need a left join in set analysis please.

    Phil Colbert

      I have 3 table all associated by a person id (SAP schema).

      These three tables all have start and end dates indicating whether a record is current or closed.

      My main person table has 1000 records. My address table has 995 and my CRB has 25.

      Using set analysis as such…

      =count( {$<
      PersonTable_StartDate= {"<=$(vNowdate)"}
      PersonTable_EndDate  = {">$(vNowdate)"}

      // CRB Status
      , CRB_BeginDate= {"<=$(vNowdate)"}
      CRB_EndDate  = {">$(vNowdate)"}

      // Address
      , AddTable_BegDate = {"<=$(vNowdate)"}
      , AddTable_EndDate = {">$(vNowdate)"}

      DISTINCT  PersonId )


      Now ideally I would have liked an open record in all of the tables and then this wouldn't present a problem. However, the CRB restriction before the Address restriction shows addresses of only those who have CRB records.

      Question is… how do I modify my set expression so I end up with Nulls in the results for CRB so I get all the Addresses and also all the people regardless of whether they have an address record.