MySql how to get `END AS` of `CASE` statement with checking another condition by `IF` or another `CASE`

Question

I have the following query what i want to do is controlling the END AS of a SELECT CASE with checking another condition.

set @lang = 'pr';

select 
  COUNT(t1.id) as total, 
  COUNT(t1.id) as 'Total', 
  CASE t2.`precence`
        WHEN '0' THEN 'Absent'
        WHEN '1' THEN 'Present'
        ELSE 'Unknown'
    END AS series,
  CONCAT(DAYNAME(t1.date),' ',DATE_FORMAT(t1.date,'%Y %M %d')) AS ticks,
  CONCAT(DATE_FORMAT(t1.date, '%a'),' ',DATE_FORMAT(t1.date,'%Y %M %d')) AS abv,
    courses.no AS label,
  CASE t2.`precence`
        WHEN '0' THEN IF(@lang = 'en','Absent Participant',IF(@lang = 'pr','غیر حاضر',IF(@lang = 'pa','test','unkown')))
        WHEN '1' THEN IF(@lang = 'en','Present Participant',IF(@lang = 'pr','حاضر',IF(@lang = 'pa','حاضر','unkown')))
        ELSE IF(@lang = 'en','Unknown State',IF(@lang = 'pr','نامعلوم',IF(@lang = 'pa','نامعلوم','unkown')))
    END AS IF(@lang = 'en','Attendance Status',IF(@lang = 'pr','وضعیت حاضری',IF(@lang = 'pa','وضعیت حاضری','Attendance State')))),
    CONCAT(DAYNAME(t1.date),' ',DATE_FORMAT(t1.date,'%Y %M %d')) AS IF(@lang = 'en','Attendance Date',IF(@lang = 'pr','تاریخ حاضری',IF(@lang = 'pa','تاریخ حاضری','Attendance Date'))),
    CONCAT(DATE_FORMAT(t1.date, '%a'),' ',DATE_FORMAT(t1.date,'%Y %M %d')) AS 'Attendance Date (abv)',
    courses.no AS 'Course Number'

FROM course_attendances as t1 left join `student_attendances` as t2 on t2.`course_attendance_id` = t1.`id`
RIGHT JOIN courses ON courses.id = t1.course_id
WHERE t1.`course_id` = '1' AND t1.date BETWEEN '2015-01-01' AND '2016-11-17'
GROUP BY t1.`date`,t2.`precence`

But in END AS section of CASE statement query stop and shows that query is wrong, how can i solve that.

Use [13.5 SQL Syntax for Prepared Statements](http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html). — wchiquito, Nov 04 '15 at 05:50
@wchiquito How about using with php variable instead of mysql variable — jones, Nov 04 '15 at 05:53

score 1 · Answer 1 · answered Nov 04 '15 at 06:08

SQL doesn't allow you to control the column name for an "AS" clause using an IF statement. The word that follows AS in this context must be a simple column name.

An SQL query produces a list of columns whose names and datatypes are defined when the query is analysed, not when the query is run.

wchiquito · Answer 2 · 2015-11-04T11:35:25.583

One option is:

mysql> SET @`_lang` := 'pr';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @`qry` := CONCAT('
     >   SELECT
     >     CASE
     >       WHEN 1 > 0 THEN \'true\'
     >       ELSE \'false\'
     >     END AS `', IF(@`_lang` = 'en', 'Attendance Status',
    ->                  IF(@`_lang` = 'pr', 'وضعیت حاضری',
    ->                    IF(@`_lang` = 'pa', 'وضعیت حاضری',
    ->                      'Attendance State'
    ->                    )
    ->                  )
    ->                ), '`'
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE `stmt` FROM @`qry`;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE `stmt`;
+-----------------------+
| وضعیت حاضری           |
+-----------------------+
| true                  |
+-----------------------+
1 row in set (0.00 sec)

mysql> DEALLOCATE PREPARE `stmt`;
Query OK, 0 rows affected (0.00 sec)

Remember, AS is optional, see 13.2.9 SELECT Syntax.

SQL Fiddle demo

MySql how to get `END AS` of `CASE` statement with checking another condition by `IF` or another `CASE`

2 Answers2