How do I query a newly formed column in SQL

Question

Hi everybody,

So I've created a new column using the 'AS' function in SQL. And, I would like to query the newly created column (after removing '() and '-') to extract cells with digit <> 10.

But I keep getting an error function. How to I query a newly created column?

Below is what I wrote:

SELECT PhoneNumber, REPLACE(REPLACE(REPLACE(PhoneNumber, ')', ''), '(', ''), '-', '') AS Justnum
FROM Person.PersonPhone
WHERE Justnum <> '10'

ERROR MESSAGE = Invalid column name 'Justnum'.

Answer 1

One cannot reference the computed column in a WHERE clause because the WHERE clause is evaluated before the SELECT in the logical processing order of the query.

Use a common table expression (WITH) so that the computed column may be used later in a WHERE claause:

WITH cte_example AS (  
	SELECT PhoneNumber, REPLACE(REPLACE(REPLACE(PhoneNumber, ')', ''), '(', ''), '-', '') AS Justnum  
	FROM Person.PersonPhone  
)  
SELECT PhoneNumber, Justnum  
FROM cte_example  
WHERE Justnum <> '10';  

If you have multiple statements in the batch, be sure the preceding statement is terminated with a semi-column to avoid syntax errors.

Answer 2

Hi @umm.shams
Refer to the Stages of Logical Query Processing：
(5)SELECT DISTINCT TOP(<top_specification>) <select_list>

(1)FROM <left_table> <join_type> JOIN <right_table> ON <on_predicate>

(2)WHERE <where_predicate>

(3)GROUP BY <group_by_specification>

(4)HAVING <having_predicate>

(6)ORDER BY <order_by_list>

In SQL, the first clause that is processed is the FROM clause, while the SELECT clause, which appears first, is processed almost last. So the ailas in Step 5 cannot be used in Step 2.

--Solution1 Use Subquery

SELECT PhoneNumber,Justnum  
FROM ( SELECT PhoneNumber, REPLACE(REPLACE(REPLACE(PhoneNumber, ')', ''), '(', ''), '-', '') AS Justnum  
       FROM Person.PersonPhone) T  
WHERE Justnum <> '10';  

--Solution2 Use CTE(common_table_expression)

;WITH CTE AS   
(  
 SELECT PhoneNumber, REPLACE(REPLACE(REPLACE(PhoneNumber, ')', ''), '(', ''), '-', '') AS Justnum  
 FROM Person.PersonPhone  
)  
SELECT PhoneNumber, Justnum  
FROM CTE  
WHERE Justnum <> '10';  

Best regards,
LiHong