Error using "greater than" in select command with case

Question

Error using "greater than" in select command with case

Navigation

#1 by (2 votes)

1

I'm trying to run the following select command:

SELECT 
estoque.id,
produto.id,
produto.descricao PRODUTO,
case saida.quant when > 0 then (estoque.quant + sum(saida.quant)) else estoque.quant end ESTOQUE
FROM estoque, saida, produto
join produto on estoque.idProduto = produto.id
join saida on estoque.id = saida.idEstoque
where estoque.idUnidade = '0'
group by estoque.id

I get an error on the line of the case where it has the symbol > . He says he's not allowed to be there.

My intention is that, if saida.quant is greater than zero, it adds with estoque.quant , when it is less than zero, list only estoque.quant

mysql select

asked by anonymous 16.08.2017 / 04:33

1 answer

How to read Json with Android Responsive site does not adapt on mobile

score 2 · Accepted Answer

According to the documentation , the syntax of your query is incorrect. It should look something like this:

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

What you can do is to reverse the order of the comparison and thus you will not need more than > . See below:

SELECT 
estoque.id,
produto.id,
produto.descricao PRODUTO,
case saida.quant when 0 then estoque.quant else (estoque.quant + sum(saida.quant)) end ESTOQUE
FROM estoque, saida, produto
join produto on estoque.idProduto = produto.id
join saida on estoque.id = saida.idEstoque
where estoque.idUnidade = '0'
group by estoque.id

If you want to consider negative values, it can be done in this way according to the same logic but with the correct syntax. See:

case when saida.quant>0 then estoque.quant else (estoque.quant + sum(saida.quant)) end...