Simplifying CASE WHEN SQL statement
Posted
by
kateroh
on Stack Overflow
See other posts from Stack Overflow
or by kateroh
Published on 2011-01-08T00:18:13Z
Indexed on
2011/01/08
0:53 UTC
Read the original article
Hit count: 120
Im trying to improve the following CASE statement to calculate the difference only once. I do it to avoid negative numbers:
SELECT (CASE WHEN ((SELECT 100 - (SELECT COUNT(CustomerId) FROM Customers)) > 0)
THEN (SELECT 100 - (SELECT COUNT(CustomerId) FROM Customers))
ELSE (0)
END)
This not only looks stupid, but also is not thread-safe. I tried the following, but I get an error message "Invalid column name 'diff'."
SELECT (CASE WHEN ((SELECT 100 - (SELECT COUNT(CustomerId) FROM Customers) as diff) > 0)
THEN (diff)
ELSE (0)
END)
How can this be simplified? Is there an in-built SQL function that already does this job?
EDIT: Sorry, forgot to mention that the select statement is inside of a view declaration, so I cant declare variables.
© Stack Overflow or respective owner