Page 1 of 1

SQLite – Avoiding NULL returns (COALESCO)

SQLite – Avoiding NULL returns (COALESCO)

Welcome to the Tweaking4All community forums!
When participating, please keep the Forum Rules in mind!

Topics for particular software or systems: Start your topic link with the name of the application or system.
Examples: "MacOS X - Your question", "MS Word - Your Tip or Trick".

Please note that switching to another language when reading a post will not work!
Posts will not have a translated counterpart.




RSS Feed

Home Forums Software Development Databases SQLite – Avoiding NULL returns (COALESCO)

Viewing 1 post (of 1 total)
  • Author
    Posts
  • 13429

    Hans
    Keymaster

    Sometimes a SELECT returns NULL values, for example when no records were found, for example in something like this:

    SELECT  (SELECT someid FROM sometable WHERE somevalue=something) ...

    The sub query COULD return a NULL value, and when trying to read this in an application that tries to convert this NULL to a number (integer for example) you will potentially run into an error – “” not an Integer;
    To Avoid NULL values, initially I thought to define the fields as NOT NULL with a DEFAULT value. But that didn’t work as I’d hoped.

    After some searching, I found the function COALESCE which takes a variable number of arguments and returns the first value in the list that is not NULL (or NULL when all values are NULL).

    So COALESCE(1,2,NULL) will return “1”, and COALESCE(NULL,”a”,”b”) will return “a”.
    So looking at out example query, we can make sure at least a number will be returned, say “0”, by doing this:

    SELECT COALESCE( (SELECT someid FROM sometable WHERE somevalue=something), 0)  ...

    So if the subquery returns NULL, COALESCE will return ‘0’.

    • This topic was modified 3 weeks, 1 day ago by Hans.
    • This topic was modified 2 weeks, 3 days ago by Hans.
Viewing 1 post (of 1 total)



You must be logged in to reply to this topic.