Unknown Column in Where Clause

Question

Why MySQL claims of not finding a column? I did specify it in 'select clause'.

MySQL 5.1.39

Error Code: 1054 Unknown column 'dt' in 'where clause'

Query:

-- Generate a Table for Every Day in 2008 Year
SELECT DATE_ADD('2008-01-01',
            INTERVAL (ones.num + tens.num + hundreds.num) DAY) dt
FROM
    (SELECT 0 num UNION ALL
     SELECT 1 num UNION ALL
     SELECT 2 num UNION ALL
     SELECT 3 num UNION ALL
     SELECT 4 num UNION ALL
     SELECT 5 num UNION ALL
     SELECT 6 num UNION ALL
     SELECT 7 num UNION ALL
     SELECT 8 num UNION ALL
     SELECT 9 num) ones
CROSS JOIN
    (SELECT 0 num UNION ALL
     SELECT 10 num UNION ALL
     SELECT 20 num UNION ALL
     SELECT 30 num UNION ALL
     SELECT 40 num UNION ALL
     SELECT 50 num UNION ALL
     SELECT 60 num UNION ALL
     SELECT 70 num UNION ALL
     SELECT 80 num UNION ALL
     SELECT 90 num) tens
CROSS JOIN
    (SELECT 0 num UNION ALL
     SELECT 100 num UNION ALL
     SELECT 200 num UNION ALL
     SELECT 300 num) hundreds
WHERE YEAR(dt) < 2009
ORDER BY dt;

It may not know `dt` in that context. Try `WHERE DATE_ADD('2008-01-01' .....` — Pekka, Jan 05 '11 at 12:23
I think MySQL allows aliases in the `having` clause but not the `where` clause. — Martin Smith, Jan 05 '11 at 12:24

score 3 · Accepted Answer · answered Jan 05 '11 at 12:24

You must reference column aliases in a HAVING clause. HAVING conditions are executed on the results set after the query has been processed, as opposed to WHERE clauses which are part of the intial query execution. Because of this HAVING is able to operate on column aliases which are only available in the results set.

-- Generate a Table for Every Day in 2008 Year
SELECT DATE_ADD('2008-01-01',
            INTERVAL (ones.num + tens.num + hundreds.num) DAY) dt
FROM
    (SELECT 0 num UNION ALL
     SELECT 1 num UNION ALL
     SELECT 2 num UNION ALL
     SELECT 3 num UNION ALL
     SELECT 4 num UNION ALL
     SELECT 5 num UNION ALL
     SELECT 6 num UNION ALL
     SELECT 7 num UNION ALL
     SELECT 8 num UNION ALL
     SELECT 9 num) ones
CROSS JOIN
    (SELECT 0 num UNION ALL
     SELECT 10 num UNION ALL
     SELECT 20 num UNION ALL
     SELECT 30 num UNION ALL
     SELECT 40 num UNION ALL
     SELECT 50 num UNION ALL
     SELECT 60 num UNION ALL
     SELECT 70 num UNION ALL
     SELECT 80 num UNION ALL
     SELECT 90 num) tens
CROSS JOIN
    (SELECT 0 num UNION ALL
     SELECT 100 num UNION ALL
     SELECT 200 num UNION ALL
     SELECT 300 num) hundreds
HAVING YEAR(dt) < 2009
ORDER BY dt;

score 1 · Answer 2 · answered Jan 06 '11 at 00:14

Having without a group by is mysql-specific. You could also try something like this:

select * from (
SELECT DATE_ADD('2008-01-01',
      INTERVAL (ones.num + tens.num + hundreds.num) DAY) dt
FROM
    (SELECT 0 num UNION ALL
     SELECT 1 num UNION ALL
     SELECT 2 num UNION ALL
     SELECT 3 num UNION ALL
     SELECT 4 num UNION ALL
     SELECT 5 num UNION ALL
     SELECT 6 num UNION ALL
     SELECT 7 num UNION ALL
     SELECT 8 num UNION ALL
     SELECT 9 num) ones
CROSS JOIN
    (SELECT 0 num UNION ALL
     SELECT 10 num UNION ALL
     SELECT 20 num UNION ALL
     SELECT 30 num UNION ALL
     SELECT 40 num UNION ALL
     SELECT 50 num UNION ALL
     SELECT 60 num UNION ALL
     SELECT 70 num UNION ALL
     SELECT 80 num UNION ALL
     SELECT 90 num) tens
CROSS JOIN
    (SELECT 0 num UNION ALL
     SELECT 100 num UNION ALL
     SELECT 200 num UNION ALL
     SELECT 300 num) hundreds
dates
where year(dates.dt) < 2009 
ORDER BY dt;

Having without a group by is not MySQL specific. You can do this in SQL Server. Use of aliases and non aggregated columns in the having clause is though. — Martin Smith, Jan 06 '11 at 00:23

score 0 · Answer 3 · answered Jan 05 '11 at 12:24

It can be a bit tricky using those named columns, but you can use 'HAVING' instead of WHERE:

SELECT DATE_ADD('2008-01-01',
      INTERVAL (ones.num + tens.num + hundreds.num) DAY) dt
FROM
    (SELECT 0 num UNION ALL
     SELECT 1 num UNION ALL
     SELECT 2 num UNION ALL
     SELECT 3 num UNION ALL
     SELECT 4 num UNION ALL
     SELECT 5 num UNION ALL
     SELECT 6 num UNION ALL
     SELECT 7 num UNION ALL
     SELECT 8 num UNION ALL
     SELECT 9 num) ones
CROSS JOIN
    (SELECT 0 num UNION ALL
     SELECT 10 num UNION ALL
     SELECT 20 num UNION ALL
     SELECT 30 num UNION ALL
     SELECT 40 num UNION ALL
     SELECT 50 num UNION ALL
     SELECT 60 num UNION ALL
     SELECT 70 num UNION ALL
     SELECT 80 num UNION ALL
     SELECT 90 num) tens
CROSS JOIN
    (SELECT 0 num UNION ALL
     SELECT 100 num UNION ALL
     SELECT 200 num UNION ALL
     SELECT 300 num) hundreds
HAVING YEAR(dt) < 2009
ORDER BY dt;

Unknown Column in Where Clause

3 Answers3

Linked