Внутреннее соединение между 2 sql с ROW_Number SQL Server

Вы не можете ссылаться на Claims в своем подзапросе, потому что он не был введен. Почему бы не переместить это соединение в основной запрос?

Select * 
from 
(
    Select 
        PolicyReference as IRIS_Policy_Ref ,
        REPLACE(SUBSTRING(Ch.ClaimSuffix,3,4),'-','') as Claims_Seq,
        CH.AccidentDate as Loss_Date,
        CH.AccidentYear as Loss_Year,
        CH.ClaimCreatedDate as Claim_Advised_Date,
        CH.NoticeDescription as Loss_Description,
        NULL  as Conv_Claim_No,
        NULL as CHI,     
        NULL as Manual, 
        BrokerRef as Broker_Code,                
        Null as Current_ACR,
        Null as Current_IBNR,
        Source ='DCT',
        ROW_NUMBER() OVER(PARTITION BY PolicyReference ORDER BY TransactionDate DESC) RowNum 
    from dbo.Policy P
    INNER JOIN dbo.Claims CH ON Ch.PolicyReference = P.PolicyReference
) PM 
where PM.RowNum = 1 

Многим нравится использовать CTE:

with table_with_rowsnums as (
Select * from (Select Distinct
PolicyReference as IRIS_Policy_Ref ,
REPLACE(SUBSTRING(Ch.ClaimSuffix,3,4),'-','') as Claims_Seq,
CH.AccidentDate as Loss_Date,
CH.AccidentYear as Loss_Year,
CH.ClaimCreatedDate as Claim_Advised_Date,
CH.NoticeDescription as Loss_Description,
NULL  as Conv_Claim_No,
NULL as CHI,     
NULL as Manual, 
BrokerRef as Broker_Code,                
Null as Current_ACR,
Null as Current_IBNR,
Source ='DCT',
ROW_NUMBER() OVER(PARTITION BY PolicyReference ORDER BY TransactionDate 
DESC) RowNum 
from dbo.Policy ) PM 
INNER JOIN dbo.Claims CH ON Ch.PolicyReference = PM.PolicyReference)
select * from  table_with_rowsnums where rownum=1

Нет необходимости использовать select distinct с row_number(). Кроме того, CH не имеет ссылки. Вам нужно сделать JOIN в подзапросе:

select * 
from (Select pm.PolicyReference as IRIS_Policy_Ref ,
             REPLACE(SUBSTRING(Ch.ClaimSuffix, 3, 4), '-', '') as Claims_Seq,
             CH.AccidentDate as Loss_Date,
             CH.AccidentYear as Loss_Year,
             CH.ClaimCreatedDate as Claim_Advised_Date,
             CH.NoticeDescription as Loss_Description,
             NULL as Conv_Claim_No,
             NULL as CHI,     
             NULL as Manual, 
             BrokerRef as Broker_Code,                
             Null as Current_ACR,
             Null as Current_IBNR,
             Source ='DCT',
             ROW_NUMBER() OVER (PARTITION BY pm.PolicyReference ORDER BY pm.TransactionDate DESC) RowNum 
         from dbo.Policy p JOIN
              dbo.Claims CH
              ON Ch.PolicyReference = PM.PolicyReference
        ) PM 
where PM.RowNum = 1 ;