CODEWITHSUNDEEP
sqldelphiinterbase
IElite
IElite

Reputation: 1848

Select AS not working in interbase

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

Upvotes: 3

Views: 5438

Answers (2)

Bharat
Bharat

Reputation: 6866

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

Upvotes: 11

Harold Sota
Harold Sota

Reputation: 7566

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

Upvotes: 0

Related Questions