Return default value in mysql query

Asked

Viewed 621 times

1

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

In this case I would like to get the value 0, I tried using the 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

  • Updating the source table is an option ?

  • no, this case of not returning value may be common, does not impact the process, but would like to identify, through select, null return cases.

2 answers

3

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

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

1

Try to use the case:

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

CASE documentation

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.