Re: IFNULL returns NULL when it shouldn't

[Martijn Tonies]

Hi Ed,

> How do I prevent IFNULL and ISNULL from returning a null?
>
> I have the following query where this is occurring,
>
> Select IFNULL(sum(qty),0)
> from inventory
> where partnumber=111
> group by partnumber;
>
> If the partnumber has never been in inventory then the sum and ifnull
> functions both return null. If the partnumber has been in inventory then
> the sum returns the correct sum but I need it to return a zero if it's
> null. It also doesn't matter if I use the ISNULL funtion or the Is Null
> operation with a IF statement.

It doesn't return NULL, it returns an empty set.

However, if you remove the GROUP BY, it will return 0 just
fine.

Why are you doing the grouping anyway?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

IFNULL returns NULL when it shouldn't

[Ed Reed]

How do I prevent IFNULL and ISNULL from returning a null?
 
I have the following query where this is occurring,
 
Select IFNULL(sum(qty),0)
from inventory
where partnumber=111
group by partnumber;
 
If the partnumber has never been in inventory then the sum and ifnull
functions both return null. If the partnumber has been in inventory then
the sum returns the correct sum but I need it to return a zero if it's
null. It also doesn't matter if I use the ISNULL funtion or the Is Null
operation with a IF statement.
 
Any suggestions?