Re: change a empty value for 0

[João Cândido de Souza Neto]

Hi.

Let me explain something about coalesce.

coalesce(field,0) return 0 if the field value equals null or the field value 

"obed" <obed.listas@stripped> escreveu na mensagem 
news:a2cca3fd0607251611sd393a2rd7a5ca8ab2bb5a1d@ style="color:#666">stripped...
> Hi. thanks ! but nop...
>
> SELECT id_ingenio,cantidad FROM detalle_transaccion WHERE cantidad IS 
> NULL;
> Empty set (0.00 sec)
>
> and with coalesce
>
> nop   :-(      it's the same problem....
>
> mysql> select id_ingenio, (select case when sum(
> coalesce(cantidad,NULL) ) is null then 0 else sum(cantidad) end from
> detalle_transaccion where id_ingenio=ingenio.id_ingenio group by
> id_ingenio) as cantidad from ingenio LIMIT 5;
> +------------+----------+
> | id_ingenio | cantidad |
> +------------+----------+
> |          1 |     NULL |
> |          2 |     NULL |
> |          3 |     NULL |
> |          4 |  2622.77 |
> |          5 |     NULL |
> +------------+----------+
> 5 rows in set (0.00 sec)
>
>
>
> the problem is that my sub select returns a empty result, it isn't a
> null value, but somthing straing is happening if i make only the sub
> select look
>
> mysql> select case when sum(cantidad) is null then 0 else
> sum(cantidad) end as a from detalle_transaccion where id_ingenio=1
> group by id_ingenio;
> Empty set (0.00 sec)
>
> i think that the group by is doing this... becouse look
>
> select case when sum(cantidad) is null then 0 else sum(cantidad) end
> as a from detalle_transaccion where id_ingenio=1;
> +------+
> | a    |
> +------+
> |    0 |
> +------+
> 1 row in set (0.01 sec)
>
>
> what can i do ?....
>
> thanks !!!!
>
>
>
>
>
> -- 
>
> http://www.obed.org.mx ---> blog