CODEWITHSUNDEEP
sql-server-2008
HaXoR
HaXoR

Reputation: 23

can case statement be applied on a column generated in a select statement?

I have generated a column in select statement now I want to apply a case statement over it. I know case statement can only be applied on the column which is present in database but I want to know is there any alternative?

My code is:

SELECT B.reg_no,B.dist_no,B.RDT_NAME,A.YTD_PQ,
RANK() OVER (PARTITION BY B.reg_no ORDER BY A.YTD_PQ DESC) AS Rank_1,
CASE Rank_1 WHEN '1' THEN 'YES' ELSE 'NO' END AS NVARCHAR(10)
FROM OTHER_AWARDS AS B 
JOIN MT_D AS A
ON A.RDT = B.RDT

now I want to know how can i apply Case on the column Rank_1 which is generated in the select statement.

Upvotes: 0

Views: 72

Answers (2)

Bohemian
Bohemian

Reputation: 424993

Yes. Wrap the query as an inner query and all columns can then be treated like regular columns, eg:

select case when computed_column ... end
from (
    select rank() ... as computed_column
    from ...
} q

which in your case would look like:

SELECT *, CASE Rank_1 WHEN '1' THEN 'YES' ELSE 'NO' END AS NVARCHAR(10)     
FROM (
  SELECT B.reg_no,B.dist_no,B.RDT_NAME,A.YTD_PQ,
  RANK() OVER (PARTITION BY B.reg_no ORDER BY A.YTD_PQ DESC) AS Rank_1
  FROM OTHER_AWARDS AS B
  JOIN MT_D AS A ON A.RDT = B.RDT
) q

Upvotes: 0

jpw
jpw

Reputation: 44881

As I recall you can't refer to a computed column on the same level of a statement as it isn't known (or processed) at the time you refer to it, but reusing the rank() function in the case statement should work, so try this:

SELECT B.reg_no,B.dist_no,B.RDT_NAME,A.YTD_PQ,
RANK() OVER (PARTITION BY B.reg_no ORDER BY A.YTD_PQ DESC) AS Rank_1,
CASE RANK() OVER (PARTITION BY B.reg_no ORDER BY A.YTD_PQ DESC) WHEN '1' THEN 'YES' ELSE 'NO' END 
FROM OTHER_AWARDS AS B 
JOIN MT_D AS A
ON A.RDT = B.RDT

Upvotes: 1

Related Questions