Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql CASE statement multiple columns

I'm interested in making a case statement that pulls values from two different columns, one of which having a calculation involved.. I'm not sure how to make this work but I'll show you what I have so far. (I have one column that shows Item Type for example Special Order items, Discontinued Items and Items that are normally stocked. And the Second column shows the Quantity on Hand - Quantity on Sales Orders to determine if the item is in stock.) Here is my query

SELECT ItemID, ItemType, (QuantityOnHand - QuantityonSalesOrders) AS Instock
CASE
WHEN ItemType = 'SP / OR' THEN 'Special Order' 
WHEN Instock < '1' THEN 'Out of Stock' 
WHEN Instock > '0' THEN 'In Stock' 
AS "Stock" FROM peachtree;
like image 651
Colin Inskeep Avatar asked Jan 09 '13 16:01

Colin Inskeep


People also ask

Can CASE statement return multiple columns?

It can only return one value. If you want the case expression to cover several return columns at once, you will have to write separate case expressions for each column.

Can we use CASE statement in MySQL?

The MySQL CASE StatementThe CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

How do you update multiple columns in SQL with different conditions?

To update multiple columns use the SET clause to specify additional columns. Just like with the single columns you specify a column and its new value, then another set of column and values. In this case each column is separated with a column.


1 Answers

For this type of request you will either have to reuse the calculation or use a subquery. This is because the alias that you are giving the Instock value is not available for use within the select list:

SELECT ItemID, 
    ItemType, 
    Instock,
    CASE
        WHEN ItemType = 'SP / OR' THEN 'Special Order' 
        WHEN Instock < '1' THEN 'Out of Stock' 
        WHEN Instock > '0' THEN 'In Stock' 
    END AS "Stock" 
FROM
(
    select ItemID, ItemType, (QuantityOnHand - QuantityonSalesOrders) AS Instock
    from peachtree
) p;

Or:

SELECT ItemID, 
    ItemType, 
    (QuantityOnHand - QuantityonSalesOrders) as Instock,
    CASE
        WHEN ItemType = 'SP / OR' THEN 'Special Order' 
        WHEN (QuantityOnHand - QuantityonSalesOrders) < '1' THEN 'Out of Stock' 
        WHEN (QuantityOnHand - QuantityonSalesOrders) > '0' THEN 'In Stock' 
    END AS "Stock" 
FROM peachtree
like image 100
Taryn Avatar answered Sep 22 '22 11:09

Taryn