How to use the column name alias from a case statement to a join statement

Question

How to use the column name alias from a case statement to a join statement

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
December 20, 2020 at 4:44 am

#3822493

Hello
While trying to use the case alias column in the join statement, getting the invalid column error.
Error msg: Invalid column name 'ST100'.
SELECT TOP (1000)
CASE
WHEN [Sx].ac_No IN (
'ARC'
,'GFT'
,'DHG'
,'STF'
,'FRX'
)
THEN '45010'
ELSE CD.Idea
END AS [ST100]
FROM CONSUL AS CD
LEFT JOIN table2 AS [Sx] ON CD.clumn1 = [Sx].column1
LEFT JOIN table3 AS [Fx] ON ST100 = Fx.texo
Is there anyway to address this need?

Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

To leverage the Alias, I think you would need to either use a common Table Expression or a sub query to expose the alias to the next statements.

Alais's are evaluated AFTER joins, but nesting them in sub queries will do what you are asking, here's an example.

WITH MyCTE
AS
(
SELECT TOP (1000)
CASE 
WHEN [Sx].ac_No IN (
'ARC'
,'GFT'
,'DHG'
,'STF'
,'FRX'
)
THEN '45010'
ELSE CD.Idea
END AS [ST100]
FROM CONSUL AS CD
)
SELECT CD.*,Sx.*,Fx.*
FROM MyCTE AS CD
LEFT JOIN table2 AS [Sx] ON CD.clumn1 = [Sx].column1

LEFT JOIN table3 AS [Fx] ON ST100 = Fx.texo

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 2

Look up the order in which the clauses in a select statement are executed in SQL. The actual SELECT list is the last one performed, so your alias doesn't exist while the other clauses are being executed. You can do some nesting (subqueries, CTE, etc.) to materialize the results you want. Then you can reference them. The basic scoping rules in SQL are something like any block structured language, but not quite.

Please post DDL and follow ANSI/ISO standards when asking for help.

ScottPletcher SSC Guru Points: 101117 More actions · Answer 3

In SQL Server, there is a way to assign a usable column alias, using CROSS APPLY:

SELECT TOP (1000) ST100
FROM CONSUL AS CD
LEFT JOIN table2 AS [Sx] ON CD.clumn1 = [Sx].column1
CROSS APPLY (
    SELECT 
    CASE
    WHEN [Sx].ac_No IN (
    'ARC'
    ,'GFT'
    ,'DHG'
    ,'STF'
    ,'FRX'
    )
    THEN '45010'
    ELSE CD.Idea
    END AS [ST100]
) AS alias1
LEFT JOIN table3 AS [Fx] ON ST100 = Fx.texo

You can even embed / stack CROSS APPLY alias references, for example:

SELECT *

FROM ( SELECT 1 AS a, 2 AS b ) AS main_data

CROSS APPLY ( SELECT a + b AS c ) AS alias1

CROSS APPLY ( SELECT c + 5 AS d ) AS alias2_using_alias1

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".