如何在JOIN关联条件中使用REPLACE转换后的字段？

你遇到的问题根源是SQL的执行顺序：FROM/JOIN子句会先于SELECT子句执行，你在SELECT里定义的Referto别名，在JOIN的ON条件触发时还未被创建，因此会提示字段无效。

下面提供三种可行的解决办法：

方法1：在JOIN条件中直接复用REPLACE表达式 #

把SELECT里的转换逻辑直接复制到JOIN的ON条件中，就能用处理后的值关联MisPerson_Main表：

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(REF.ReferTo,'[MisPhDirContact]',' '), '[MisPerson]',' ') AS Referto
      ,REF.[ReferralStartDate] AS Referral_date
      ,REF.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 REPLACE(REPLACE(REF.ReferTo,'[MisPhDirContact]',' '), '[MisPerson]',' ') = PER.UnvUserID
WHERE REF.RowUpdateDateTime > '2023-01-01 00:00:00.000' 
  AND REF.RowUpdateDateTime < '2024-01-01 00:00:00.000' 
  AND REF.ReferTo IS NOT NULL 

方法2：用CTE预处理字段 #

通过公共表表达式（CTE）先计算出转换后的Referto字段，后续关联直接使用该字段，代码更整洁：

WITH ReferralWithCleanedRef AS (
    SELECT
        OmOrdID,
        RowUpdateDateTime,
        ReferralStartDate,
        OrderProvider,
        ReferTo,
        REPLACE(REPLACE(ReferTo,'[MisPhDirContact]',' '), '[MisPerson]',' ') AS Referto
    FROM livefdb.dbo.OmOrd_Referral
    WHERE RowUpdateDateTime > '2023-01-01 00:00:00.000' 
      AND RowUpdateDateTime < '2024-01-01 00:00:00.000' 
      AND ReferTo IS NOT NULL
)
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
      ,REF.Referto
      ,REF.ReferralStartDate AS Referral_date
      ,REF.OrderProvider    AS Ordering_provider
FROM ReferralWithCleanedRef 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 REF.Referto = PER.UnvUserID

方法3：用子查询预处理字段 #

和CTE逻辑类似，通过子查询先完成字段转换，再进行后续关联：

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
      ,REF.Referto
      ,REF.ReferralStartDate AS Referral_date
      ,REF.OrderProvider    AS Ordering_provider
FROM (
    SELECT
        OmOrdID,
        RowUpdateDateTime,
        ReferralStartDate,
        OrderProvider,
        REPLACE(REPLACE(ReferTo,'[MisPhDirContact]',' '), '[MisPerson]',' ') AS Referto
    FROM livefdb.dbo.OmOrd_Referral
    WHERE RowUpdateDateTime > '2023-01-01 00:00:00.000' 
      AND RowUpdateDateTime < '2024-01-01 00:00:00.000' 
      AND ReferTo IS NOT NULL
) 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 REF.Referto = PER.UnvUserID

内容的提问来源于stack exchange，提问作者DJT