title: Converting ANSI operators in SQL
body: <p>Need help in converting the following 2 queries to ANSI compatible code. My code is using =* operators and I need to convert them into right outer join. I have given it a good attempt, however, not able to get the desired results. </p>
  
  <h1>Query 1</h1>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT m.MatterID, m.MatterType,
      m.HasSuppressionOrder, m.Complexity, 
      m.DateReceived, m.DateCompleted,
      ph.OfficerID, pe.Surname + ', ' + pe.GivenNames, h1.Result
  FROM   CMS.MATTER m,
      CMS.PHASE ph,
      CMS.PERSON pe,
      CMS.HEARING h1
  WHERE             
      (m.DateCompleted BETWEEN '2015-10-29 00:00:00' and '2015-10-30 00:00:00') 
      AND (ph.MatterID = m.MatterID 
      and ph.PhaseSeq = (SELECT MAX(ph2.PhaseSeq) FROM CMS.PHASE ph2 where ph2.MatterID = m.MatterID)) 
      AND (pe.PersonNo = (SELECT mp.PersonNo FROM CMS.MATTER_PERSON mp
              WHERE mp.MatterID = m.MatterID and mp.MatterPersonType = 'DEF')) 
      AND (h1.MatterID =* m.MatterID and --NON ANSI JOIN OPERATORS THAT NEED CONVERSION
      h1.PhaseSeq =* ph.PhaseSeq and --NON ANSI JOIN OPERATORS THAT NEED CONVERSION
      h1.HearingDateTime =* (SELECT MAX(h2.HearingDateTime)FROM CMS.HEARING h2 WHERE h2.MatterID = m.MatterID))
  ORDER BY m.MatterID
  </code></pre>
  
  <h1>Query 2</h1>
  
  <pre><code>SELECT  m.MatterID,
      m.MatterType,
      m.HasSuppressionOrder,
      m.DateReceived,
      ISNULL (ph.OfficerID, 'No Phase'),
      ISNULL (pe.Surname, 'No Accused Registered') + ', ' + pe.GivenNames,
      m.Complexity,
      m.ProgramCode,
      m.Comments,
      m.StaffGroup,
      m.StaffSubGroup
  FROM   CMS.MATTER m,
      CMS.PHASE ph, 
      CMS.PERSON pe
  WHERE m.DateReceived BETWEEN '2015-09-30 00:00:00' AND '2015-10-30 00:00:00'
      AND       ph.MatterID =* m.MatterID -- Taken care
      AND       ph.PhaseSeq =* (
          SELECT  MAX( PhaseSeq )
          FROM   CMS.PHASE
          WHERE MatterID = m.MatterID -- Taken care
      )
      AND pe.PersonNo =* (
          SELECT  PersonNo
          FROM   CMS.MATTER_PERSON
          WHERE MatterID = m.MatterID
             AND       MatterPersonType = 'DEF'
      )
  </code></pre>
  
  <p>I tried converting query 2 and wrote the following:
  </p>
  
  <pre><code>SELECT  m.MatterID,
      m.MatterType,
      m.HasSuppressionOrder,
      m.DateReceived,
      ISNULL (ph.OfficerID, 'No Phase'),
      ISNULL (pe.Surname, 'No Accused Registered') + ', ' + pe.GivenNames,
      m.Complexity,
      m.ProgramCode,
      m.Comments,
      m.StaffGroup,
      m.StaffSubGroup
  FROM   CMS.PHASE ph
  right join CMS.MATTER m ON ph.MatterID = m.MatterID
  RIGHT JOIN CMS.PERSON pe ON ((SELECT  PersonNo FROM   CMS.MATTER_PERSON WHERE MatterID = m.MatterID AND MatterPersonType = 'DEF')= pe.PersonNo)
  RIGHT JOIN CMS.PHASE ph2 ON ((SELECT  MAX(PhaseSeq) FROM CMS.PHASE ph WHERE ph.MatterID = m.MatterID)=ph2.PhaseSeq)
  WHERE (m.DateReceived BETWEEN '2015-09-30 00:00:00' AND '2015-10-30 00:00:00')
  </code></pre>
  
  <p>===========================================</p>
  
  <p>However, something is not right.</p>