CODEWITHSUNDEEP
sql-server
atomoutside
atomoutside

Reputation: 189

SQL - Alias in CASE statements

I have this piece of code (please look below). I keep getting error: "Invalid column name 'SuppFinish2'

SELECT

CASE
    WHEN [RegFinish] IS NULL THEN ''
    ELSE [RegFinish]
END AS [RegFinish],

CASE
    WHEN [SuppFinish] IS NULL THEN ''
    ELSE [SuppFinish]
END AS [SuppFinish2],

CASE
    WHEN [RegFinish]<[SuppFinish2] THEN '1'
    ELSE '0'
END AS [TEST]

FROM TABLE

Is it because of [SuppFinish2] being an alias? Thanks!

Upvotes: 2

Views: 11058

Answers (5)

sagi
sagi

Reputation: 40481

You cant use those aliases in the same level as you created them, becuase they are not existing yet.. wrap your query with another select like this:

SELECT * ,
       CASE
            WHEN [RegFinish]<[SuppFinish2] THEN '1'
            ELSE '0'
       END AS [TEST]
FROM (
    SELECT
        [ID],
        CASE
            WHEN [RegFinish] IS NULL THEN ''
            ELSE [RegFinish]
        END AS [RegFinish],
        CASE
            WHEN [SuppFinish] IS NULL THEN ''
            ELSE [SuppFinish]
        END AS [SuppFinish2],
    FROM TABLE)

Upvotes: 1

CiucaS
CiucaS

Reputation: 2128

SELECT ISNULL([RegFinish],'') as  [RegFinish]
     , ISNULL([SuppFinish],'') as [SuppFinish2], CASE 
WHEN  
  ISNULL([RegFinish],'') < ISNULL([SuppFinish],'') THEN 1
                                                   ELSE 0 
END  AS [TEST]
FROM TABLE

Why not use ISNULL instead of CASE? The problem with your query is that [SuppFinish2] is an alias not an column and can only be used in ORDER BY clause

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You cannot, at the same time, set and access an alias in the SELECT clause. I would suggest rewriting your query using CROSS APPLY:

SELECT t1.[RegFinish],
       t2.[SuppFinish],
       CASE
          WHEN t1.[RegFinish] < t2.[SuppFinish] THEN '1'
          ELSE '0'
       END AS [TEST]
FROM TABLE
CROSS APPLY (SELECT COALESCE([RegFinish], '') AS [RegFinish]) AS t1
CROSS APPLY (SELECT COALESCE([SuppFinish], '') AS [SuppFinish]) AS t2

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28910

As you said its due to alias and aliased columns can be referenced only on order by to logical query flow order

with cte
as
(
SELECT
CASE
    WHEN [RegFinish] IS NULL THEN ''
    ELSE [RegFinish]
END AS [RegFinish],

CASE
    WHEN [SuppFinish] IS NULL THEN ''
    ELSE [SuppFinish]
END AS [SuppFinish2]
FROM TABLE
)
select 
CASE
    WHEN [RegFinish]<[SuppFinish2] THEN '1'
    ELSE '0'
END AS [TEST]
from cte

Upvotes: 2

Jeremy
Jeremy

Reputation: 4838

In order to reference aliased columns, you can use a derived table (or CTE, but that is not shown here)

Select *, CASE
WHEN [RegFinish]<[SuppFinish2] THEN '1'
ELSE '0'
END AS [TEST] From
(
SELECT

CASE
    WHEN [RegFinish] IS NULL THEN ''
    ELSE [RegFinish]
END AS [RegFinish],

CASE
    WHEN [SuppFinish] IS NULL THEN ''
    ELSE [SuppFinish]
END AS [SuppFinish2]
) T1
FROM TABLE

Upvotes: 1

Related Questions