Return zero if NULL

I have a piece of MySQL that is hitting some empty records, how would I set this to return 0 if NULL? Thank You!

Trie开发者_JS百科d this:

sprintf(queryString, "SELECT COUNT(*) FROM tblURLIP
     WHERE IP = '%s' AND IPStatus = '1' AND IPMax     = '0' 
         AND IPType ='3'", ipSrc); 

Didn't work:

sprintf(queryString, "SELECT COUNT(*) FROM tblURLIP 
          WHERE IP = '%s' AND IPStatus = '1' AND IPMax = '0' 
             AND IPType ='3'", ipSrc,NULL),0); 

You can usually wrap things that return NULL in a coalesce function which gives you the first non-null value:

select coalesce (some-nullable-thing, 0 ) from ...

However, count() should never return NULL. If there are no records satisfying the clauses, it should just return 0.

If your problem lies not in the SQL but in the fact that ipSrc itself is NULL, this is a simple matter of morphing it before processing it with the sprintf. To convert a NULL to 0 and leave everything else untouched, you can use something like:

sprintf (queryString,
    "SELECT COUNT(*) FROM tblURLIP"
    " WHERE IP = '%s'"
    " AND IPStatus = '1'"
    " AND IPMax = '0'"
    " AND IPType ='3'",
    (ipSrc == NULL) ? "0" : ipSrc);   // <-- conversion happens here.

That seems to be your desired behaviour.

The snippet of code you post is pure C (it's obviously building up a query string for SQL, but there's no SQL being done here, just building up a string).

My only interpretation of your question that makes sense to me is that sometimes ipSrc is NULL, sometimes it points to a valid character string.

Assuming that is the case, I think this will do what I think you are asking:

sprintf(queryString, "SELECT COUNT(*) FROM tblURLIP
     WHERE IP = '%s' AND IPStatus = '1' AND IPMax     = '0' 
         AND IPType ='3'", ipSrc == 0 ? "0" : ipSrc);

The key part is the ? : operator combination. If this is too-obfuscated C for your comfort you could also do this:

if (ipSrc == 0) // instead of 0 you can use NULL if that better suits your style
    ipSrc = "0";
sprintf(queryString, "SELECT COUNT(*) FROM tblURLIP
         WHERE IP = '%s' AND IPStatus = '1' AND IPMax     = '0' 
             AND IPType ='3'", ipSrc);