Cfrgtkky

I'm getting syntax errors on the >,<, and = operators here and I'm really not sure why. I've tried enclosing reorder_quantity + in_stock in () and I still get the same errors. What am I missing here?

I'm trying to use the following code to generate a list of amounts that I should order for certain products. Im using SQL to do so because I order the products in oz. but they are inventoried in mLs, and my POS can't handle the conversion natively.

select itemnum, itemname, in_stock, reorder_level, 

    case reorder_quantity + in_stock 

        when reorder_quantity + in_stock > reorder_level 

           then reorder_quantity/29.5735                                                

        when reorder_quantity + in_stock = reorder_level 

           then reorder_quantity/29.5735*2

        when reorder_quantity + in_stock < reorder_level 

           then reorder_quantity/29.5735*2

        else 0 

        end as order_amount_oz



from inventory 



group by itemnum 



and in_stock < reorder_level

You're mixing the two types of case syntax:

1. case expression when value1 then output1 when value2 then output2 else output3 end


2. case when expression = value1 then output1 when expression = value2 then output2 else output3 end

together, which is a syntax error. Chose one or the other.

Try this refactoring and simplification that‘s the same as your intention:

reorder_quantity / 29.5735 * 

    case when reorder_quantity + in_stock > reorder_level then 1 else 2 end as order_amount_oz

If I understand correctly you can try to remove group by and using parentheses

select itemnum, itemname, in_stock, reorder_level, 

    case 

        when (reorder_quantity + in_stock) > reorder_level 

           then reorder_quantity/29.5735                                                

        when (reorder_quantity + in_stock) = reorder_level 

           then reorder_quantity/(29.5735*2)

        when (reorder_quantity + in_stock) < reorder_level 

           then reorder_quantity/(29.5735*2)

        else 0 

        end as order_amount_oz



from inventory