[SOLVED] Referencing a column alias in the WHERE clause

Referencing a column alias in the WHERE clause

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!

Solution

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.