Hi. Let me explain something about coalesce.
coalesce(field,0) return 0 if the field value equals null or the field value if it´s not null. "obed" <[EMAIL PROTECTED]> escreveu na mensagem news:[EMAIL PROTECTED] > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]