5 Replies Latest reply: Mar 25, 2014 5:57 AM by phillip colbert RSS

    Need a left join in set analysis please.

    phillip 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.