Inner Join between 2 sql having ROW_Number SQL Server

Question

I have gone through the particular posts but couldn't still fix the issue I having with Inner Join of two SQL statements with a Row_number function call in them.

Trying to pull data from two tables. I am using Row_Number to get the distinct policies as there are lots of duplicate values. I can't figure out what is wrong in the Inner Join part.

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
where 
    PM.RowNum = 1 

Error message sample -

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "Ch.ClaimSuffix" could not be bound.

Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "Ch.AccidentDate" could not be bound.

Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "CH.AccidentYear" could not be bound.

What am I doing wrong? It is not recognizing the claims table columns.

Any leads would be greatly appreciated. I am stuck in this since morning.

Thanks !!

Answer 1

You can't reference Claims in your subquery like that because it hasn't been introduced. Why not move that join to the primary query like this?

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