Once in a while I'm asked on how to return NULL in a SELECT. This can be a strange request, and usually relates to two also strange situations:
- UNLOAD TO file.unl SELECT "" FROM table
This will create the unload file with "|\ |" (note that there's a space following the backslash)
- SELECT NULL FROM table
This simple SQL instruction raises a 201 (syntax error) exception
- "" (empty string)
This is fine for Informix, but people tend to use UNLOAD files to move data from Informix into other applications. And other applications don't usually like this notation. So how can we create an unload file with a "NULL" column, meaning "||" (pipe, pipe). The first obvious answer leads to problem 2):
Why does Informix raise an error if we use NULL in the projection clause (SELECT list)? It's seems an error. Specially if you're used to other databases where this works.
Well, the answer is simple. Informix is very strict with the concept of NULL. NULL is the absence of value. So, if we're instructing the SELECT to return an absence of value it raises an unsolvable problem: What is the data type of an absence of value?! Of course Informix could assume some data type, but again, the engine is very strict. So it won't take decisions of this kind for you and consequently the use of NULL in this context is not acceptable (and it raises a syntax error) So, what is the solution? Simple:
SELECT NULL::CHAR FROM table
SELECT CAST(NULL AS CHAR) FROM table
This simply defines the data type of the result set to return to the client.
Another option would be to create a stored procedure which return a CHAR and inside return the NULL value. Something like:
CREATE PROCEDURE ret_null() RETURNING CHAR;
SELECT ret_null() FROM table
But this is more complex and less efficient.
There are much more things to say about NULL, but I'll leave that for future articles.