Menu
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 ;)
596
Isn't this good enough for T-SQL? This isn't what the requester wanted, but is very useful to know that you can use if statements outside a select statement. EXISTS is good because it kicks out of the search loop if item is found. A COUNT runs until the end of table rows.
You cannot use if inside expression, if is a control-of-flow keyword. Convert it to another case statement.
The CASE expression compares an expression to a set of expression (when_expression_1, when_expression_2, when_expression_3, …) using the equality operator (=). If you want to use other comparison operators such as greater than (>), less than (<), etc., you use the searched CASE expression.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
