mysql select case multiple columns as

Question

I want to set multiple fields with something like this, how can I make it work?

Select TableId,
    CASE
        WHEN StartTime > Appt THEN
            Field AS Field_1
            FieldId AS FieldId_1
        WHEN StartTime BETWEEN Appt AND DATE_ADD(Appt, INTERVAL 1 MONTH) THEN
            Field AS Field_2
            FieldId AS FieldId_2
    END
FROM TableA;

It doesn't work, the syntax is wrong near AS Field_1.

Answer 1

You're trying to perform something similar to macro expansion in a SQL statement, which you cannot do. The simplest way to solve your problem is to write the SQL dynamically in your application code, and execute that statement conditionally from the app.

If you must do it within SQL, you have a couple of options.

Each CASE is a single expression, so you can only produce output fields singly, by having a separate CASE for each column:

SELECT ID,
  CASE WHEN StartTime > Appt THEN Field_A 
       WHEN StartTime BETWEEN Appt AND DATE_ADD(Appt, INTERVAL 1 MONTH) THEN
        Field_B
  END AS field1,
  CASE WHEN StartTime > Appt THEN Field_C 
       WHEN StartTime BETWEEN Appt AND DATE_ADD(Appt, INTERVAL 1 MONTH) THEN
        Field_D
  END AS field2
FROM TableA;

Alternatively, use the statement form of CASE (or IF, which might be simpler) within a stored procedure, to contain multiple versions of your statement and execute them conditionally.