Referencing a column alias in the WHERE clause

Question

Is there any way to return an "AS" result using WHERE or otherwise?

I'm doing a SUBSTRING on the FONE1 column and trying to return the DDD = 31 but I get the error, Code: 1054. Unknown column 'DDD' in 'where clause' 0.000 sec

-->

SELECT  **SUBSTRING(FONE1,1,2) AS DDD**, FONE1, F1STA,LASTCALL
FROM discador_processados
WHERE fila_mailing = 2638
AND F1STA ='ANSWER'
AND CLASSE1 IN ('VC2','VC3')
**AND DDD = 31**
AND LASTCALL BETWEEN '2020-10-02 00:00:00' AND '2020-10-30 23:59:59'

The idea would be to get the return below

DDD    FONE1     F1STA      LASTCALL 
31  31999999999 ANSWER  2020-10-02 09:08:13
31  31999999999 ANSWER  2020-10-02 09:09:16
31  31999999999 ANSWER  2020-10-02 09:17:41

Thanks!

Tag your question with the database you are using. But you cannot re-use an alias in the `WHERE` clause. You need to repeat the expression, use a subquery, use a CTE, or use a lateral join to define the value. — Gordon Linoff, Nov 12 '20 at 12:34
I've tagged your request `mysql`, because "Code: 1054. Unknown column" sounds like MySQL. Please correct this, if it's wrong. What data type is `FONE1`? — Thorsten Kettner, Nov 12 '20 at 12:42
use ` and SUBSTRING(FONE1,1,2)=31` as I know you cannot use an alias in 'where' closure — LightNight, Nov 12 '20 at 13:39

score 1 · Accepted Answer · answered Nov 12 '20 at 12:36

I would recommend writing the WHERE as:

WHERE fila_mailing = 2638 AND
      F1STA ='ANSWER' AND
      CLASSE1 IN ('VC2', 'VC3') AND
      FONE1 LIKE '31%' AND
      LASTCALL >= '2020-10-02' AND
      LASTCALL < '2020-10-31'

Note the changes to the logic:

FONE1 appears to be a string, so the comparison uses string operations.
The DATETIME comparisons uses >= and < rather than BETWEEN so the last second on the last day is not missed.
The date format is simplified.

score 1 · Answer 2 · answered Nov 12 '20 at 13:11

You cannot use output column alias in WHERE clause, because the output column expressionis not evaluated yet and hense simply not exists.

But you may do this in HAVING clause:

SELECT SUBSTRING(FONE1,1,2) AS DDD, FONE1, F1STA,LASTCALL
FROM discador_processados
WHERE fila_mailing = 2638
  AND F1STA ='ANSWER'
  AND CLASSE1 IN ('VC2','VC3')
  AND LASTCALL BETWEEN '2020-10-02 00:00:00' AND '2020-10-30 23:59:59'
HAVING DDD = 31

And pay attention to Gordon Linoff's answer - it is very useful.

Referencing a column alias in the WHERE clause

2 Answers2