TSQL - Issues and errors using a function as a derived table

Question

I am having a difficult time fulfilling a business requirement the way I want to. I am open to suggestions to do this different ways as well.

Essentially, there are quite a few data entry errors in the context of Employee and Dependent addresses. The Employee's might be something like 123 SW 5th St., where the dependents may be entered as 123 S.W. 5th St. I need to dynamically compare the addresses to see if they are the same--i.e the dependent lives with the employee.

The client and I have decided that I should split the address by a space delimiter and compare the first three splits of the Employee and Dependent, and if two of them match, then the addresses are the same. The code to split the strings is working properly.

The approach I took was this:

• Use a function to split the dependent address into three strings and return them in a table variable
• Use a function to split the employee address into three strings and return them in a table variable
• Use these tables as derived tables, and join them together purposefully creating a many to many join
• If the count of the records returned from that many to many join is >=2, then the addresses are the same.

Below is what the code looks like:

UPDATE #CoDepBenTable
SET ContactAddressIsDifferent = CASE WHEN COUNT(*) > 1
                                 THEN 'N'
                                 ELSE 'Y'
                            END
FROM (SELECT * 
      FROM dbo.fn_CompareAddresses(#CoDepBenTable.DEV_EmpAddress)
     )AS D
    JOIN (SELECT *
          FROM dbo.fn_CompareAddresses(#CoDepBenTable.ContactAddressLine1)
         ) AS E
      ON E.AddressPart = D.AddressPart

The error I am getting is The multi-part identifier "#CoDepBenTable.DEV_EmpAddress" could not be bound., and The multi-part identifier "#CoDepBenTable.ContactAddressLine1" could not be bound.

The function is created in the dbo schema in the same database as the data is coming from, although I first put the data into a temp table and then try to update the temp table.

Is there any way to get around this? I cannot figure out why I am receiving errors here.

Answer 1

I think the code below is a good guide to do what you want, mark ContactAddressIsDifferent with Y or N

UPDATE @CoDepBenTable
SET ContactAddressIsDifferent = 'Y'

declare @CoDepBenTableTEMP table(ID int)

INSERT INTO @CoDepBenTableTEMP
SELECT ID FROM 
 (
 SELECT * FROM @CoDepBenTable T1
 CROSS APPLY dbo.fn_CompareAddresses(T1.DEV_EmpAddress,',')
 ) A 
 INNER JOIN
 (
 SELECT * FROM @CoDepBenTable T2 
 CROSS APPLY dbo.fn_CompareAddresses(T2.ContactAddressLine1,',')
 ) B ON A.AddressPart = B.AddressPart

UPDATE T 
SET ContactAddressIsDifferent = 'N'
FROM @CoDepBenTable T INNER JOIN @CoDepBenTableTEMP TEMP
ON T.ID = TEMP.ID