I have an SQL statement that is not allowing me to use the converted field name from a Replace. I need to use the converted field for my Join statement.

This is the SQL code that I am using, but what I want to use is Referto. When I try this, I get the error that this is an invalid field.

 SELECT
       REF.[OmOrdID] AS Order_ID
      ,REGM.VisitID  AS Visit_ID
      ,REGM.AccountNumber AS Account_number
      ,HIMM.Name AS Patient_name
          ,REF.[RowUpdateDateTime] AS Date_order_entered
      ,REPLACE (REPLACE(ReferTo,'[MisPhDirContact]',' '), '[MisPerson]',' ') AS Referto
              ,[ReferralStartDate] AS Referral_date
      ,OrderProvider    AS Ordering_provider
   FROM livefdb.dbo.OmOrd_Referral REF
   LEFT JOIN livefdb.dbo.OmOrd_Main ORDM ON (REF.OmOrdID      = ORDM.OmOrdID)
       JOIN livefdb.dbo.OmOrd_Main2 ORDM2 ON (REF.OmOrdID    = ORDM2.OmOrdID)
       JOIN livefdb.dbo.RegAcct_Main REGM ON (ORDM.VisitID   = REGM.VisitID)
       JOIN livefdb.dbo.HimRec_Main HIMM ON  (REGM.PatientID  = HIMM.PatientID)
       JOIN livefdb.dbo.MisPerson_Main PER ON (ReferTo        = PER.UnvUserID)

   WHERE REF.RowUpdateDateTime > '2023-01-01 00:00:00.000' and REF.RowUpdateDateTime <  
    '2024-01-01 00:00:00.000' and ReferTo IS NOT NULL