3

works

select payeeid, EXTRACT(WEEKDAY FROM checkdate) as DOW, 
(bankcleared - checkdate) as DateDiff  
from Master  
where (bankcleared is not null)  
order by payeeid, DOW, DateDiff

adding DateDiff to Where - does not work 

select payeeid, EXTRACT(WEEKDAY FROM checkdate) as DOW, 
(bankcleared - checkdate) as DateDiff  
from Master  
where (bankcleared is not null)  AND (DateDiff >= 1)  
order by payeeid, DOW, DateDiff
Bharat
  • 6,828
  • 5
  • 35
  • 56
IElite
  • 1,818
  • 9
  • 39
  • 64
  • sorry, i also meant to ask....why adding DateDiff to where causes an error - DateDiff Column unknown – IElite Feb 18 '11 at 13:56
  • Because DATEDIFF is a built-in function, which retrieves the date part difference between two date time stamps. –  Feb 18 '11 at 14:01

2 Answers2

11

You can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses.

Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined.

Try this

select payeeid, EXTRACT(WEEKDAY FROM checkdate) as DOW, 
(bankcleared - checkdate) as DateDiff
from Master
where (bankcleared is not null) AND ((bankcleared - checkdate)>= 1)
order by payeeid, DOW, DateDiff

For more info go through these links

Can you use an alias in the WHERE clause in mysql?

Unknown Column In Where Clause

Community
  • 1
  • 1
Bharat
  • 6,828
  • 5
  • 35
  • 56
0
select payeeid, 
       EXTRACT(WEEKDAY FROM checkdate) as DOW, 
       (bankcleared - checkdate) as DateDiff
from Master
WHERE (bankcleared is not null) 
AND   ((bankcleared - checkdate)>= 1)
Order by  payeeid, DOW, DateDiff
Harold Sota
  • 7,490
  • 12
  • 58
  • 84