How can I use nested cases in mySQL?

Question

I want to work with nested cases:

SELECT *, 
    GROUP_CONCAT(DISTINCT CONCAT(
    CASE
      WHEN data.name NOT "field" THEN   
        CASE
          WHEN data.category = "A" THEN "A"
          WHEN data.category = "B" THEN "B"
          ELSE "C"
        END,data.name
    END
   )ORDER BY FIND_IN_SET(data.category,"B,C,A") ASC, data.name ASC SEPARATOR " <br>") AS result
  FROM data;');

But I get an error message:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'data.name END ' at line 11

Remove the nested case and you'll end up with `CASE WHEN data.name NOT "field" THEN [...] , data.name END`. This isn't valid SQL. I think you'll need to move the `data.name` after the second `END`. — waka, Feb 16 '18 at 09:54
@waka `A` or `B` or `C` should only appear when `data.name` is not `field` — peace_love, Feb 16 '18 at 09:56
@Jarla: I got that, but the problem is the `data.name` before the second end. That's why I suggested removing the nested case, so you could see the error more clearly. — waka, Feb 16 '18 at 09:58
@Jarla check my answer below, you had not added parenthesis in the nested case and also you put data.name inside the nested case. check the answer below. — Abdul Rahman, Feb 16 '18 at 10:02

score 1 · Accepted Answer · answered Feb 16 '18 at 09:58

1

You have some syntax error in your query. Run the following code in your query editor, and it will be ok.

SELECT 
 *,
GROUP_CONCAT(
DISTINCT CONCAT(
  CASE
    WHEN data.name = "field" 
    THEN (
      CASE
        WHEN data.category = "A" 
        THEN "A" 
        WHEN data.category = "B" 
        THEN "B" 
        ELSE "C" 
      END
    ) 
  END,
    data.name
) 
ORDER BY FIND_IN_SET(data.category, "B,C,A") ASC,
data.name ASC SEPARATOR " <br>"
) AS result 
 FROM DATA;

answered Feb 16 '18 at 09:58

Abdul Rahman

1,399
2
17
35

1

@Jarla compare your code with mine, you have two syntax errors in your code, if you will change it or just copy and paste the code, it will show you the result as aspected. – Abdul Rahman Feb 16 '18 at 10:06
@Jarla compare your code with mine, you have two syntax errors in your code, if you will change it or just copy and paste the code, it will show you the result as aspected. – Abdul Rahman Feb 16 '18 at 10:06

How can I use nested cases in mySQL?

1 Answers1