mysqlsqlselectmysql-workbenchcase-statement

MySQL Workbench - SELECT is not valid at this position with this server version when using CASE WHEN statement

When implementing a basic CASE WHEN statement in MySQL Workbench, I am getting the following error "SELECT is not valid at this position for this server version. Expecting FOR, LOCK, TABLES, VALUES, WITH, '('".

Here is my SQL query:

SELECT mode_dsc, Weight, rate, min_cost, 
CASE 
    WHEN (Weight*rate) < min_cost THEN min_cost AS Cost
    WHEN (Weight*rate) > min_cost THEN (Weight*rate) AS Cost
END AS Cost
FROM OrderLineRate
WHERE mode_dsc = "AIR";

The OrderLineRate table consists of the following:

OrderID ProductID mode_dsc Weight minm_wgh_qty max_wgh_qty rate min_cost
1447133055 1678648 AIR 1.463 0 99.99 $0.05 $1.50

Software: MySQL Workbench (version 8.0.22.CE) to interact with a MySQL Server(version 8.0.22)

Solution

  • as cost is only appropriate after the end of the case.

    SELECT mode_dsc, Weight, rate, min_cost, 
CASE 
    WHEN (Weight*rate) < min_cost THEN min_cost 
    WHEN (Weight*rate) > min_cost THEN (Weight*rate) 
END AS Cost
FROM OrderLineRate
WHERE mode_dsc = "AIR";