Search code examples
mysqlmysql-error-1064

CASE statement in MYSQL trigger

Most of the info about this topic seems to refer strictly to select statements. I am attempting to implement some code in a trigger.

DECLARE v_fname, v_mname,v_lname,v_suffix, v_position VARCHAR(80);
DECLARE v_name VARCHAR(120);

SELECT first_name, middle_name, last_name, suffix into v_fname, v_mname, v_lname, v_suffix FROM people where objid = NEW.person_id;

SELECT v_fname, v_mname, v_lname, v_suffix,
CASE
  WHEN ((v_mname != "" OR v_mname IS NOT NULL) AND (v_suffix != "" OR v_suffix IS NOT NULL)) THEN SET v_name = CONCAT(v_fname,v_mname,v_lname,v_suffix)
  WHEN ((v_mname = "" OR v_mname IS NULL) AND (v_suffix != "" OR v_suffix IS NOT NULL)) THEN SET v_name = CONCAT(v_fname, v_lname, v_suffix)
  WHEN ((v_mname != "" OR v_mname IS NOT NULL) AND (v_suffix = "" OR v_suffix IS NULL)) THEN SET v_name = CONCAT(v_fname," ",v_mname," ",v_lname)
  ELSE SET v_name = CONCAT(v_fname," ",v_lname)
END CASE;

When I attempt to save the code I get an error: enter image description here

The basic goal is to clean up the name data and output a concatenated list.

The problem is that some parts of the name are nonexistent, which causes problems in formatting or delivery of information when CONCAT encounters a NULL.

I've attempted using COALESCE and CONCAT_WS and they are not producing the results I want.

The result has 4 possibilities:

Joe J. Jones, Jr
Joe Jones, Jr
Joe J. Jones
Joe Jones

I had not used CASE before and it seemed as though it might be an elegant solution.

I'm going to go back to IF THEN ELSE but I'm curious if I exceeded what is possible here.

It's possible that I haven't solved this problem well, so I would appreciate insights or comments to help settle my curiosity here.

Solution

  • You should not use SET for assign a value to a column ..you just need select the columns eg: assuming you want the result v_name columns 

        SELECT v_fname, v_mname, v_lname, v_suffix,
    CASE
      WHEN ((v_mname != "" OR v_mname IS NOT NULL) 
                        AND (v_suffix != "" OR v_suffix IS NOT NULL)) 
                THEN CONCAT(v_fname,v_mname,v_lname,v_suffix)
      WHEN ((v_mname = "" OR v_mname IS NULL) 
                        AND (v_suffix != "" OR v_suffix IS NOT NULL)) 
                THEN CONCAT(v_fname, v_lname, v_suffix)
      WHEN ((v_mname != "" OR v_mname IS NOT NULL) 
                        AND (v_suffix = "" OR v_suffix IS NULL)) 
                THEN  CONCAT(v_fname," ",v_mname," ",v_lname)
      ELSE CONCAT(v_fname," ",v_lname) as v_name
    END CASE;