CODEWITHSUNDEEP
mysql
Gerardo Guevara
Gerardo Guevara

Reputation: 303

What's wrong in this query on MySQL 5.7 using CASE?

I'm trying to use CASE on MySQ L5.7 on HeidiSQl,

I want to use CASE to call a table and use the CASE with the intervals 0, 1-100 and 100 or more.

But It's doesn't work

SELECT ventas.Ventas_Id AS ID, ventas.Ventas_Fecha AS fecha, ventas.Ventas_Neto AS neto
CASE  Ventas_Total
   WHEN ventas.Ventas_Total = 0 THEN 'C'
   WHEN ventas.Ventas_Total > 100 THEN 'S'
   WHEN ventas.Ventas_Total  < 100 THEN 'L'                       
END AS "Categoria"
FROM ventas

I have this error message

SQL Error (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 'CASE ventas.Ventas_Total

   WHEN ventas.Ventas_Total = 0 THEN 'C'

at line 4

Upvotes: 0

Views: 1452

Answers (2)

Gerardo Guevara
Gerardo Guevara

Reputation: 303

Thanks, now it's working.

SELECT ventas.Ventas_Id AS ID, ventas.Ventas_Fecha AS fecha, ventas.Ventas_Neto AS neto,

    ventas.Ventas_Total AS Total

CASE ventas.Ventas_Total

    WHEN ventas.Ventas_Total = 0 THEN 'C'


      WHEN ventas.Ventas_Total > 100 THEN 'S'

    WHEN ventas.Ventas_Total  < 100 THEN 'L'   END AS Categoria                



   FROM ventas

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

There are two styles of CASE expression, and you are actually mixing syntax from both types. Because you have an inequality in the CASE expression, you can only use this style:

CASE WHEN Ventas_Total = 0 THEN 'C'
     WHEN Ventas_Total > 100 THEN 'S'
     WHEN Ventas_Total < 100 THEN 'L'
END AS Categoria

The other style of CASE expression only works when you want to do only equality comparisons, e.g.

CASE Ventas_Total
    WHEN 1 THEN 'A'
    WHEN 2 THEN 'B'
    WHEN 3 THEN 'C' END AS some_alias

Upvotes: 4

Related Questions