MySQL SELECT with case to replace integer field with text

Question

Hi i need to replace an integer field with the respective text, it is not a variable thing, using a join would be a waste for this.

The table structure is

CREATE TABLE `mesas_cab` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cid` int(11) DEFAULT NULL,
  `vid` int(11) DEFAULT NULL,
  `status` int(11) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and my query is

SELECT
mesas_cab.id,
mesas_cab.cid,
mesas_cab.vid,
mesas_cab.status,
usuarios.NOME,
clientes.RAZAOSOCIAL,
CASE mesas_cab.status
    WHEN 0 THEN 'Livre'
    WHEN 1 THEN 'Ocupada'
    WHEN 2 THEN 'Fechada'
END CASE as status_str
FROM
mesas_cab
LEFT JOIN usuarios ON mesas_cab.vid = usuarios.CODIGO
LEFT JOIN clientes ON mesas_cab.cid = clientes.CODIGO

i have seen this answer replace integer with string in query

but my query is still giving me syntax errors

[SQL] SELECT
mesas_cab.id,
mesas_cab.cid,
mesas_cab.vid,
mesas_cab.status,
usuarios.NOME,
clientes.RAZAOSOCIAL,
CASE mesas_cab.status
    WHEN 0 THEN 'Livre'
    WHEN 1 THEN 'Ocupada'
    WHEN 2 THEN 'Fechada'
END CASE as status_str
FROM
mesas_cab
LEFT JOIN usuarios ON mesas_cab.vid = usuarios.CODIGO
LEFT JOIN clientes ON mesas_cab.cid = clientes.CODIGO

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CASE as status_str
FROM
mesas_cab
LEFT JOIN usuarios ON mesas_cab.vid = usuar' at line 12

but when i remove the whole case statement the query works almost as planned(the joins are okay)

you don't need CASE after the END i.e. CASE mesas_cab.status ... END AS status_str — Nick
– Nick, Commented Feb 3, 2020 at 12:42
@Nick is right. Also you may want an ELSE clause in your CASE to cover unexpected status values. — O. Jones
– O. Jones, Commented Feb 3, 2020 at 12:49

felix · Accepted Answer · 2020-02-03 12:56:34Z

1

The case statement doesn't seem to be done correctly. The correct format is usually CASE WHEN x = 1 then 'y' WHEN x = 2 then 'z' ELSE NULL END AS column_name

So in our case it should be something like


SELECT
mesas_cab.id,
mesas_cab.cid,
mesas_cab.vid,
mesas_cab.status,
usuarios.NOME,
clientes.RAZAOSOCIAL,
CASE 
    WHEN mesas_cab.status = 0 THEN 'Livre'
    WHEN mesas_cab.status = 1 THEN 'Ocupada'
    WHEN mesas_cab.status = 2 THEN 'Fechada'
    ELSE NULL
END as status_str
FROM
mesas_cab
LEFT JOIN usuarios ON mesas_cab.vid = usuarios.CODIGO
LEFT JOIN clientes ON mesas_cab.cid = clientes.CODIGO

answered Feb 3, 2020 at 12:56

felix

562 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rodrigo P. Over a year ago

Thank you so much, i was following the dev.mysql.com/doc/refman/5.7/en/case.html too and it was confusing.

Collectives™ on Stack Overflow

MySQL SELECT with case to replace integer field with text

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related