Jochem van Dieten <[EMAIL PROTECTED]> wrote on 11/07/2005 03:32:25 PM:
> 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
Alternatively, you *might* be able to get away with using per-row
variables:
SELECT @Pass := if(score<50, -5 ,0) passing_score
, @Fail := if(score>50,1,0) failing_score
, @pctAtt := if(totaldays!=0, attendance/totaldays, NULL)
Percent_Attendance
, if(@[EMAIL PROTECTED] !=0, @fail/@pass * @pctAtt, NULL)
FROM schoolwork;
I don't have your data so I can't test this to make sure it's going to
evaluate in the correct order. I don't use per-row evaluation enough to
know off the op of my head if this will work or not. However, I have seen
this pattern elsewhere so I offer it as something to try.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
