SQL Server : having and where clauses for column names selected with "as"

Question

I am trying to get the results for a similar query on Microsoft SQL Server:

SELECT 
    A, B, C AS DANUMBER 
FROM 
    TABLE
WHERE 
    B = 'SOMEVALUE'
HAVING 
    DANUMBER > 0

However, I can not use the column I have selected with "AS" in SQL Server. The DANUMBER is simply unknown to the server.

Every example I looked up informs about showing aggregate functions, nothing solved my problem. This query works on mySQL, but not on SQL Server.

I think I may be missing some syntax or something...

Answer 1

MySQL extends the use of the HAVING clause to recognize column aliases defined in the SELECT. You cannot assume this behavior in other databases (it doesn't work in SQL Server and Oracle, for instance, it does work in Postgres). You have three choices: repeat the expression, use a subquery, or use a CTE.

The first is the best for code that might be running in both SQL Server and MySQL:

SELECT A, B, C AS DANUMBER 
FROM TABLE
WHERE B = 'SOMEVALUE'
GROUP BY A, B, C
HAVING C > 0;

or:

SELECT A, B, C AS DANUMBER 
FROM TABLE
WHERE B = 'SOMEVALUE' AND 
      C > 0;