Returning null....

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:

1. UNLOAD TO file.unl SELECT "" FROM table
   This will create the unload file with "|\ |" (note that there's a space following the backslash)
2. SELECT NULL FROM table
   This simple SQL instruction raises a 201 (syntax error) exception

Let's explain the first issue. Unload files (typically pipe separated) generated by the UNLOAD instruction were created as a way to export/import data into an Informix database table. This means Informix has to understand, by looking at what's in the middle of two consecutive pipes ("|"), what was the original value. Given this: "||" (pipe followed immediately by pipe), and assuming we're dealing with a VARCHAR value, what would be the original value? I could give you two options:

1. NULL
2. "" (empty string)

Having two options is not really an option. When we LOAD the file, we want to be sure that the inserted value is exactly the original value. Given the two options what would we INSERT? A NULL value or an empty string? This is why we need to identify the NULL differently. And this is done by using "|\ |" (pipe, backslash, space, pipe).
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

or

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;
RETURN NULL;
END PROCEDURE;

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.

Posted in: 201,informix,NULL,projection clause,returning null,select list,syntax error