List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:July 16 2004 6:49pm
Subject:Re: IFNULL returns NULL when it shouldn't
View as plain text  
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

Thread
IFNULL returns NULL when it shouldn'tEd Reed16 Jul
  • Re: IFNULL returns NULL when it shouldn'tMartijn Tonies16 Jul