Column name is invalid while using SQL joins

Question

I came across a problem that I do not understand.

SELECT T1.CUSTOMER_ID,CONVERT(DATE,T1.DOB,105)   [DATE_OF_BIRTH],
    T2.TOTAL_AMT,
    DATEDIFF(YEAR,CONVERT(DATE,T1.DOB,105), GETDATE()) as AGE
FROM CUSTOMER T1
  INNER JOIN TRANSACTIONS T2 ON T1.CUSTOMER_ID=T2.CUST_ID
WHERE AGE>=25 and AGE <=35

Whenever I run this query it gives an error:

age is an invalid column

I am trying to find out those customers whose age lies between 25 to 35.

1) Please don't YELL. 2) MySQL or SQL Server 3) Please use the code format option in the editor to make your question readable. — Dale K, Apr 29 '20 at 08:49
Age doesn't exist yet, since WHERE is evaluated before the SELECT list. — jarlh, Apr 29 '20 at 08:49

score 1 · Answer 1 · answered Apr 29 '20 at 08:50

In your query, Age doesn't exist yet, since WHERE is evaluated before the SELECT list.

Use a derived table:

select * from
(
  SELECT T1.CUSTOMER_ID,CONVERT(DATE,T1.DOB,105)   [DATE_OF_BIRTH],
      T2.TOTAL_AMT,
      DATEDIFF(YEAR,CONVERT(DATE,T1.DOB,105), GETDATE()) as AGE
  FROM CUSTOMER T1
    INNER JOIN TRANSACTIONS T2 ON T1.CUSTOMER_ID=T2.CUST_ID
) dt
WHERE AGE>=25 and AGE <=35

score 0 · Answer 2 · edited Apr 29 '20 at 08:55

0

Have you heard about logical query processing?

SELECT T1.CUSTOMER_ID,CONVERT(DATE,T1.DOB,105) [DATE_OF_BIRTH],
    T2.TOTAL_AMT,
    DATEDIFF(YEAR,CONVERT(DATE,T1.DOB,105), GETDATE()) as AGE
FROM CUSTOMER T1
  INNER JOIN TRANSACTIONS T2 ON T1.CUSTOMER_ID=T2.CUST_ID
WHERE DATEDIFF(YEAR,CONVERT(DATE,T1.DOB,105), GETDATE()) >=25 and         
DATEDIFF(YEAR,CONVERT(DATE,T1.DOB,105), GETDATE()) <= 35

edited Apr 29 '20 at 08:55

Suraj Kumar

5,547
8
20
42

answered Apr 29 '20 at 08:53

Amira Bedhiafi

8,088
6
24
60

Did you forget the code format button? – Dale K Apr 29 '20 at 08:55

Column name is invalid while using SQL joins

2 Answers2