dbreienrk1 dbreienrk1 - 9 months ago 36
SQL Question

Join based on temp column

I have two tables that need to be joined, but the only similar column has excess data that needs to be stripped. I would just modify the tables, but I only have read access to them. So, I strip the unneeded text out of the table and add a temp column, but I cannot join to it. I get the error:


Invalid column name 'TempJoin'


SELECT
CASE WHEN CHARINDEX('- ExtraText',a.Column1)>0 THEN LEFT(a.Column1, (CHARINDEX('- ExtraText', a.Column1))-1)
WHEN CHARINDEX('- ExtraText',a.Column1)=0 THEN a.Column1
END AS TempJoin
,a.Column1
,b.Column2
FROM Table1 as a
LEFT JOIN Table2 as b WITH(NOLOCK) ON b.Column2=TempJoin

Answer Source

Easiest way would be to wrap this in a CTE. Also, be careful using NOLOCK, unless you have an explicit reason.

WITH cte AS (
    SELECT
        CASE WHEN CHARINDEX('- ExtraText',a.Column1) > 0
             THEN LEFT(a.Column1, (CHARINDEX('- ExtraText', a.Column1))-1)
             WHEN CHARINDEX('- ExtraText',a.Column1) = 0
             THEN a.Column1
        END AS TempJoin,
        a.Column1
    FROM Table1 AS a
)

SELECT *
FROM cte
LEFT JOIN Table2 AS b WITH(NOLOCK) ON b.Column2 = TempJoin;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download