I would like to use CASE statement in SELECT.
I select from user table, and (as one attribute) I also use nested SQL:
SELECT
registrationDate,
(SELECT COUNT(*) FROM Articles WHERE userId = Users.userId) as articleNumber,
hobbies, ...
FROM USERS
and then I would like to do a CASE statement to get rank of user (rank is dependent on articleNumber).
I tried like this:
SELECT
registrationDate,
(SELECT COUNT(*) FROM Articles WHERE Articles.userId = Users.userId) as articleNumber,
ranking =
CASE
WHEN articleNumber < 2 THEN 'Ama'
WHEN articleNumber < 5 THEN 'SemiAma'
WHEN articleNumber < 7 THEN 'Good'
WHEN articleNumber < 9 THEN 'Better'
WHEN articleNumber < 12 THEN 'Best'
ELSE 'Outstanding'
END,
hobbies, etc...
FROM USERS
Parsing displays no error, but when I try to run it I get error:
Msg 207, Level 16, State 1, Procedure GetUserList, Line XY
Invalid column name 'articleNumber'.
CASE doesn't "recognize" my nested SELECT, I guess.
I helped myself with some other solutions like SQL Server 2008 - Case / If statements in SELECT Clause but nothing seems to work.
I also didn't find any similar problem with '<' and '>' comparison.
Any help would be greatly appreciated ;)
This question is related to
sql
sql-server-2008
tsql
select
case
Please select the same in the outer select. You can't access the alias name in the same query.
SELECT *, (CASE
WHEN articleNumber < 2 THEN 'Ama'
WHEN articleNumber < 5 THEN 'SemiAma'
WHEN articleNumber < 7 THEN 'Good'
WHEN articleNumber < 9 THEN 'Better'
WHEN articleNumber < 12 THEN 'Best'
ELSE 'Outstanding'
END) AS ranking
FROM(
SELECT registrationDate, (SELECT COUNT(*) FROM Articles WHERE Articles.userId = Users.userId) as articleNumber,
hobbies, etc...
FROM USERS
)x
Should be:
SELECT registrationDate,
(SELECT CASE
WHEN COUNT(*)< 2 THEN 'Ama'
WHEN COUNT(*)< 5 THEN 'SemiAma'
WHEN COUNT(*)< 7 THEN 'Good'
WHEN COUNT(*)< 9 THEN 'Better'
WHEN COUNT(*)< 12 THEN 'Best'
ELSE 'Outstanding'
END as a FROM Articles
WHERE Articles.userId = Users.userId) as ranking,
(SELECT COUNT(*)
FROM Articles
WHERE userId = Users.userId) as articleNumber,
hobbies, etc...
FROM USERS
You can try with this:
WITH CTE_A As (SELECT COUNT(*) as articleNumber,A.UserID as UserID FROM Articles A
Inner Join Users U
on A.userId = U.userId
Group By A.userId , U.userId ),
B as (Select us.registrationDate,
CASE
WHEN CTE_A.articleNumber < 2 THEN 'Ama'
WHEN CTE_A.articleNumber < 5 THEN 'SemiAma'
WHEN CTE_A.articleNumber < 7 THEN 'Good'
WHEN CTE_A.articleNumber < 9 THEN 'Better'
WHEN CTE_A.articleNumber < 12 THEN 'Best'
ELSE 'Outstanding'
END as Ranking,
us.hobbies, etc...
FROM USERS Us Inner Join CTE_A
on CTE_A.UserID=us.UserID)
Select * from B
Source: Stackoverflow.com