I've got the following Generated Column modification that is throwing an error for me. I modified it recently with a similar nested case but realized after the fact I didn't save the query and I need to update one check so it needs to be updated.
ALTER TABLE
`t1` MODIFY COLUMN `criteria` GENERATED ALWAYS AS(
CASE WHEN `status` = 'Active' THEN
CASE WHEN `exempt` = 'FALSE' AND `age` < 35 THEN
CASE WHEN `experience` = "Pro" AND `rule` = 'Descendent' THEN 6
WHEN `experience` = "Pro" AND `rule` != 'Descendent' THEN 5
WHEN `experience` = 'Division I' AND `rule` = 'Descendent' THEN 5
WHEN `experience` = 'Division I' AND `rule` != 'Descendent' THEN 4
WHEN `experience` = 'Division II' AND `rule` = 'Descendent' THEN 4
WHEN `experience` = 'Division II' AND `rule` != 'Descendent' THEN 3
WHEN `experience` = 'Division III' AND `rule` = 'Descendent' THEN 3
WHEN `experience` = 'Division III' AND `rule` != 'Descendent' THEN 2
WHEN `experience` = 'NAIA' AND `rule` = 'Descendent' THEN 3
WHEN `experience` = 'NAIA' AND `rule` != 'Descendent' THEN 2
WHEN `experience` = 'Junior College' AND `rule` = 'Descendent' THEN 2
WHEN `experience` = 'Junior College' AND `rule` != 'Descendent' THEN 1
WHEN `experience` = 'High School' AND `rule` = 'Descendent' THEN 1
ELSE 0
END
ELSE 0
END
ELSE 0
END
) STORED
Can anyone tell me what I'm missing here? I can't for the life of me figure out what I'm missing. Thanks in advance! Here is the error from MySql:
MySQL said: Documentation
#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 'GENERATED ALWAYS AS(
CASE WHEN `status` = 'Active' THEN
CA' at line 2
Even for generated columns, you must specify a data type as part of the column definition.
ALTER TABLE
`t1` MODIFY COLUMN `criteria` _______________________ GENERATED ALWAYS AS ...
^ data type goes here ^