Error while using Case when Alias in join statement-CodePudding

SELECT T1.COL1
    ,CASE 
        WHEN T1.COL2 = 111
            THEN 'A'
        WHEN T1.COL2 = 222
            THEN 'B'
        ELSE 'C'
        END AS DT
    ,T2.COL1
FROM TABLE1 T1
LEFT JOIN TABLE2 T2 ON T1.COL1 = DT;

error- invalid identifier DT

I want to use and verify the condition using case when alias in join condition which is giving error

NOTE - UPDATED CODE

SELECT T1.COL1
        ,CASE 
            WHEN T1.COL2 = 111
                THEN 'A'
            WHEN T1.COL2 = 222
                THEN 'B'
            ELSE 'C'
            END AS DT
        ,T2.COL1
    FROM TABLE1 T1
    LEFT JOIN TABLE2 T2 ON T1.COL1 = CASE 
            WHEN T1.COL2 = 111
                THEN 'A'
            WHEN T1.COL2 = 222
                THEN 'B'
            ELSE 'C'
            END;

THis one is working. Any other way other than this?

CodePudding user response：

Unfortunately your new query still makes no sense. As noted previously, your join does not involve any column from TABLE2. Perhaps you obfuscated table names to the point you added this logic error by accident? Here is one way to avoid the huge effort to copy/paste the case expression code.

with cte as (
   select *, 
   case COL2 
        when 111 then 'A' 
        when 222 then 'B' 
        else 'C' end as DT
   from dbo.TABLE1 
)
select ... 
  from cte left join dbo.TABLE2 as t2
    on cte.Col1 = cte.DT
order by ... ;

If this case expression is commonly used, you could create a computed column and use it for your "join". That does not address the logic flaw but does address reusability.

CodePudding user response：

If you place the assembly of the custom column in a table expression, then the new column gets officially named and can be used on any other expression.

For example:

select t1.*, T2.COL1
from ( -- this is a "table expression" named "t1"
  SELECT T1.COL1
    ,CASE 
        WHEN T1.COL2 = 111
            THEN 'A'
        WHEN T1.COL2 = 222
            THEN 'B'
        ELSE 'C'
        END AS DT
  FROM TABLE1
) t1
LEFT JOIN TABLE2 T2 ON T2.COL1 = t1.DT;