CODEWITHSUNDEEP
mysqlsqlwhere-clausecolumn-alias
Mr Poke
Mr Poke

Reputation: 25

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!

Upvotes: 0

Views: 147

Answers (2)

Akina
Akina

Reputation: 42622

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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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.

Upvotes: 1

Related Questions