CODEWITHSUNDEEP
mysqlsql
Zar
Zar

Reputation: 6882

Using AS value in later on in query

Consider the following example query:

SELECT foo.bar,
DATEDIFF(
    # Some more advanced logic, such as IF(,,), which shouldn't be copy pasted
) as bazValue
FROM foo
WHERE bazValue >= CURDATE() # <-- This doesn't work

How can I make the bazValue available later on in the query? I'd prefer this, since I believe that it's enough to maintain the code in one place if possible.

Upvotes: 0

Views: 74

Answers (3)

user359040
user359040

Reputation:

There are a couple of ways around this problem that you can use in MySQL:

By using an inline view (this should work in most other versions of SQL, too):

select * from
(SELECT foo.bar,
 DATEDIFF(
     # Some more advanced logic, such as IF(,,), which shouldn't be copy pasted
 ) as bazValue
 FROM foo) buz
WHERE bazValue >= CURDATE()

By using a HAVING clause (using column aliases in HAVING clauses is specific to MySQL):

SELECT foo.bar,
DATEDIFF(
    # Some more advanced logic, such as IF(,,), which shouldn't be copy pasted
) as bazValue
FROM foo
HAVING bazValue >= CURDATE()

Upvotes: 1

makciook
makciook

Reputation: 1555

As MySQL doesn't support CTE, consider using inline view:

SELECT foo.bar,
FROM foo, 
(SELECT DATEDIFF(
    # Some more advanced logic, such as IF(,,), which shouldn't be copy pasted
    ) as bazValue
) AS iv
WHERE iv.bazValue >= CURDATE()

Upvotes: 1

eggyal
eggyal

Reputation: 125865

As documented under Problems with Column Aliases:

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined. For example, the following query is illegal:

SELECT id, COUNT(*) AS cnt FROM tbl_name
 WHERE cnt > 0 GROUP BY id;

The WHERE clause determines which rows should be included in the GROUP BY clause, but it refers to the alias of a column value that is not known until after the rows have been selected, and grouped by the GROUP BY.

You can however reuse the aliased expression, and if it uses deterministic functions the query optimiser will ensure that cached results are reused:

SELECT foo.bar,
       DATEDIFF(
         -- your arguments
       ) as bazValue
FROM   foo
WHERE  DATEDIFF(
         -- your arguments
       ) >= CURDATE()

Alternatively, you can move the filter into a HAVING clause (where aliased columns will already have been calculated and are therefore available) - but performance will suffer as indexes cannot be used and the filter will not be applied until after results have been compiled.

Upvotes: 1

Related Questions