Select CASE keyword not recognized and unexpected token-CodePudding

SELECT *,
    ADDTIME(lastMove, maxzettijd) AS zettenTot,
    CASE
        WHEN zettenTot < NOW()
        THEN 1
        ELSE 0
        END AS zettijdOverschrijding
FROM games 
WHERE gameID = 2787392
LIMIT 1

lastMove = DATETIME field (0000-00-00 00:00:00)
maxzettijd = TIME field. (000:00:00)

Errors: Keyword not recognized. (near "AS" at position 162) Unexpected token. (near "zettijdOverschrijding" at position 165)

Without CASE the query works.

In CASE part: I compare present time against the expired time (zettenTot). If turn is expired, zettijdOverschrijding = 1 (turn expired, game lost)

EDIT: table added

CREATE TABLE `games` (
`gameID` int(20) UNSIGNED NOT NULL,
`whitePlayer` mediumint(9) DEFAULT NULL,
`blackPlayer` mediumint(9) DEFAULT NULL,
`gameMessage`  
 enum('','playerInvited','inviteDeclined','draw','playerResigned',
 'checkMate',' 
 VERLOPEN','ONGELDIG','tijdoverschrijding','ingetrokken',
'stalemate','zettijdoverschrijding') NOT NULL,
`messageFrom` enum('','black','white','Admin') NOT NULL,
`wit_rem_aanvraag` datetime NOT NULL,
`zwart_rem_aanvraag` datetime NOT NULL,
`rem_antw_zwart` datetime NOT NULL,
`rem_antw_wit` datetime NOT NULL,
`dateCreated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`lastMove` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`ratingset` enum('notset','set') NOT NULL DEFAULT 'notset',
`rating_white_start` int(11) DEFAULT NULL,
`rating_white_end` int(11) NOT NULL DEFAULT '0',
`rating_black_start` int(11) DEFAULT NULL,
`rating_black_end` int(11) NOT NULL DEFAULT '0',
`notitie_wit` text NOT NULL,
`notitie_zwart` text NOT NULL,
`vak_wit` date NOT NULL DEFAULT '0000-00-00',
`vak_zwart` date NOT NULL DEFAULT '0000-00-00',
`her_invite` tinyint(4) NOT NULL DEFAULT '0',
`pionpromo` tinyint(4) NOT NULL DEFAULT '0',
`beurtaan` tinytext NOT NULL,
`whiteNick` tinytext,
`blackNick` tinytext,
`kibW` enum('ja','nee') NOT NULL DEFAULT 'ja',
`kibZ` enum('ja','nee') NOT NULL DEFAULT 'ja',
`comment` tinytext NOT NULL,
`witOnline` datetime NOT NULL,
`zwartOnline` datetime NOT NULL,
`chataanvraagdoor` int(10) UNSIGNED NOT NULL,
`chataanvraagtijd` datetime NOT NULL DEFAULT '1900-00-00 00:00:00',
`ftoernooi` enum('0','1') NOT NULL DEFAULT '0',
`tafel` enum('0','1') NOT NULL DEFAULT '0',
`ratingrange` smallint(5) UNSIGNED NOT NULL DEFAULT '500',
`speeltijd` mediumint(3) UNSIGNED DEFAULT NULL,
`speeltijdwit` mediumint(8) UNSIGNED NOT NULL,
`speeltijdzwart` mediumint(8) UNSIGNED NOT NULL,
`maxzettijd` time NOT NULL,
`startklok` datetime NOT NULL,
`opener` mediumint(8) UNSIGNED DEFAULT NULL,
`ts_ladder` tinyint(3) UNSIGNED DEFAULT NULL COMMENT 'teamschaken 
laddertoernooi',
`t_partij` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
`tt` tinyint(3) UNSIGNED NOT NULL COMMENT 'teamtoernooi',
`rated` tinyint(3) UNSIGNED NOT NULL DEFAULT '1',
`klok` tinyint(4) NOT NULL DEFAULT '0',
`toernooisoort` tinyint(10) UNSIGNED NOT NULL DEFAULT '0' COMMENT '0 = 
 geen 
 toernooi, 1 = tsreg',
`vakmodus` tinyint(4) NOT NULL DEFAULT '0',
`50zetten` tinyint(3) UNSIGNED NOT NULL DEFAULT '0'
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='herinvite: 1=uitnodiging, 
 2 voor lopende partij';

CodePudding user response：

I think your issue's caused by using alias column inside CASE statement. You could try to update your query:

SELECT *,
       ADDTIME(lastmove, maxzettijd) AS zettenTot,
       (CASE
         WHEN ADDTIME(lastmove, maxzettijd) < NOW() THEN 1
         ELSE 0
       END)                           AS zettijdOverschrijding
FROM   games
WHERE  gameid = 2787392
LIMIT  1