Link to home
Start Free TrialLog in
Avatar of kirin0
kirin0

asked on

What is the proper way to use an AS term in a WHERE clause in mysql?

The following select works just fine:

SELECT `date` FROM `idx_date` WHERE `date` >= '2010-01-01' AND `date` <= '2010-12-13'

but the following generates an error "Unknown column 'd' in 'where clause'"

SELECT `date` AS `d` FROM `idx_date` WHERE `d` >= '2010-01-01' AND `d` <= '2010-12-13'

What is the proper way to use the alias in the select?

I often run into it in cases where I'm aliasing a COUNT() or DISTINCT()

Avatar of Loganathan Natarajan
Loganathan Natarajan
Flag of India image

You may not use aliases in WHERE clause,

ref. http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html
SELECT `date` AS `d` FROM `idx_date` WHERE `d` >= '2010-01-01' AND `d` <= '2010-12-13'  ...

Will not work as

 "aliases in where clause is not allowed"...
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
that's totally per Your consideration but as in where clause actually possible for casting purposes when You cast something "AS OF " SOME OTHER TYPE ,may only be available in last versions
Avatar of kirin0
kirin0

ASKER

Thanks, that clarifies the confusion