Re: Can't reference column aliases

[Jochem van Dieten]

On 11/7/05, mos wrote:
> Why isn't there a way to reference column aliases in the columns list or
> where clause?

Because the SQL standad says so. See chapter 7 of ISO/IEC 9075-2:2003.


> select if(score<50,-5,0) failing_score, if(score>50, 1, 0) passing_score,
> attendance/totaldays Percent_Attendance ,
> failing_score/passing_score*percent_attendance
> from schoolwork
>
> (There is no logic to the columns so please don't try to replace the code
> with something simpler)

Not simpler, but equivalent and preventing double execution:

SELECT *, failing_score/passing_score*percent_attendance
FROM (
SELECT
  if(score<50,-5,0) failing_score,
  if(score>50, 1, 0) passing_score,
  attendance/totaldays Percent_Attendance
FROM
  schoolwork
) tmp_result

Jochem