MySQL CASE statement on a column alias

I am performing an IF on a column and giving the result an alias. The CASE statement that uses the alias doe not work (error – sessionDate does not exist in field list). However, if I substitute the alias for a column name it does work. How do I get around this please?

MySQL (if I use csm_pat_exe_date.pedate_date in the CASE statement it works):

IF (csm_pat_exe_date.pedate_date IS NULL, ses_date, csm_pat_exe_date.pedate_date) AS sessionDate,
CASE WHEN DAYOFWEEK(sessionDate) = 1 THEN 'Sun'
    WHEN DAYOFWEEK(sessionDate) = 2 THEN 'Mon'
    WHEN DAYOFWEEK(sessionDate) = 3 THEN 'Tue'
    WHEN DAYOFWEEK(sessionDate) = 4 THEN 'Wed'
    WHEN DAYOFWEEK(sessionDate) = 5 THEN 'Thu'
    WHEN DAYOFWEEK(sessionDate) = 6 THEN 'Fri'
    WHEN DAYOFWEEK(sessionDate) = 7 THEN 'Sat'
    ELSE ''
END AS weekday,

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Expressions in the select-list cannot reference aliases defined in the same select-list. There’s technically no defined order of evaluation for the expressions, so no expression can depend on another expression in the same list.

You can either repeat the expression that the alias is based on:

CASE WHEN DAYOFWEEK(IF (
  csm_pat_exe_date.pedate_date IS NULL, 
  ses_date, 
  csm_pat_exe_date.pedate_date)) = 1 THEN 'Sun'
...

but you would have to repeat the same IF() expression in each WHEN clause.

Or else you can define the alias in a subquery and then the outer query can reference the alias

SELECT CASE WHEN DAYOFWEEK(t.sessionDate) = 1 THEN 'Sun' 
      WHEN DAYOFWEEK(t.sessionDate) = 2 THEN 'Mon'
  ... END AS weekday
FROM (
  SELECT IF (
    csm_pat_exe_date.pedate_date IS NULL, 
    ses_date, 
    csm_pat_exe_date.pedate_date) AS sessionDate
  ...
) AS t

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0