Return 0 if the value does not exist in the table

Question

I am using select to check if there is SEQ_RESUMO in the table. If there is this returning me the value of SEQ_RESUMO, ate ai ok. But when the value does not exist in the table, according to the select I did, it was to return 0, but it is returning NULL.

SELECT 
CASE
    WHEN COUNT(1) = 0 THEN 0
    WHEN COUNT(1) > 0 THEN SEQ_RESUMO
END AS sequence
FROM INICIO_FIM_COLETA WHERE seq_picking = '244582'
GROUP BY SEQ_RESUMO

Answer 1

I believe it would be something like this, but I have no way to test it at the moment.

SELECT COALESCE( (SELECT SEQ_RESUMO FROM INICIO_FIM_COLETA WHERE seq_picking = '244582'), 0) as sequence 
from INICIO_FIM_COLETA  
group by SEQ_RESUMO

Answer 2

Use the function COALESCE. Remember that NULL is not the same thing as zero.

SELECT SEQ_RESUMO, COALESCE(COUNT(1), 0) AS sequence
FROM INICIO_FIM_COLETA WHERE seq_picking = '244582'
GROUP BY SEQ_RESUMO;

But in case there is no searched oregsitro is return is zero records, which is different from returning a record with a NULL field. Try:

SELECT SEQ_RESUMO, COALESCE(COUNT(1), 0) AS sequence
FROM INICIO_FIM_COLETA WHERE seq_picking = '244582'
GROUP BY SEQ_RESUMO
UNION
SELECT '???' AS seq_resumo, 0 AS sequence 
FROM NOT EXISTS(SELECT * FROM INICIO_FIM_COLETA WHERE seq_picking = '244582');

Answer 3

makes a change to the table Voce wants, specified in the whole column.. so where null replaces to a default value like "0"...

example:

ALTER TABLE estoque 
CHANGE COLUMN qtd DOUBLE NULL DEFAULT 0 ;