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: 153
        
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