Condition in MySQL - CASE X = Y THEN Where - Is it possible?

Question

Condition in MySQL - CASE X = Y THEN Where - Is it possible?

Navigation

#1 by (2 votes)

1

I need to get the products from my site.

In it, there are two types: Physical and digital . I would like that if the products were physical, show only those that had above 0 in the stock. If it is digital, you do not need this Where in stock.

I'm doing it this way, but it's obviously giving Syntax Error.

SELECT * FROM sistema_produto p 
WHERE proAtivo = 'S' 
AND proExcluido = 'N' 
AND (
    CASE 'proTipo' = "F" THEN proEstoque > 0
)

Where am I going wrong?

mysql

asked by anonymous 01.12.2017 / 00:27

1 answer

Access WebService localhost - Android Studio How to delete the line that automatically generates in a gridView windowns form

score 2 · Accepted Answer

CASE is very versatile, and can be used in many contexts, but in your specific situation it is neither necessary nor appropriate.

Using AND and OR already solves:

SELECT * FROM sistema_produto p 
WHERE proAtivo = 'S' 
AND proExcluido = 'N' 
AND ( 'proTipo' != "F" OR proEstoque > 0 )

That is:

If the proTipo is not is F , always show;
or , if it is F , it only shows if proEstoque is greater than zero.

Note: In MySQL and MariaDB, whenever you need it, you have the function IF( condição, valor se verdadeiro, valor se falso ) which is more elegant than CASE (but less portable).

link