Return default value in mysql query

Question

Return default value in mysql query

Navigation

#1 by (3 votes)
#2 by (1 votes)

1

I have a query that, in certain situations, does not return a value.

In this case I would like to get a value of 0, I tried to use ifnull , but without success.

SELECT ifnull((valor * t1.PBRT),'0') AS C_MP
            FROM engenharia_custo_mp t0
                ,(
                    SELECT MEDIDA1
                        ,PBRT
                    FROM engenharia_produto
                    WHERE codigo = '0304502500701'
                    ) t1
                ,(
                    SELECT MAT_PRIMAPK
                    FROM engenharia_materia_prima
                    WHERE DESCRICAO = (
                            SELECT MAT_PRIMAFK
                            FROM engenharia_produto
                            WHERE codigo = '0304502500701'
                            )
                    ) t2
            WHERE t1.MEDIDA1 = t0.ESPESSURA
                AND t2.MAT_PRIMAPK = t0.MAT_PRIMAFK2

mysql

asked by anonymous 19.01.2015 / 19:53

2 answers

OR returns result not expected Avoid infinite loop of errors

score 3 · Answer 1

3

If the fields in your table are of type INT, the result of this operation will be 0, since there is no NULL in the INTEGER fields

SELECT COALESCE(valor * t1.PBRT, 0) AS C_MP 
   FROM....

19.01.2015 / 20:26

score 1 · Answer 2

1

Try to use case :

SELECT CASE WHEN t1.PBRT > 0 THEN valor * t1.PBRT ELSE 0 END CASE AS C_MP

CASE Documentation

19.01.2015 / 20:18