Sometimes we want to extract a literal value with a SELECT. This works perfectly fine if the literal value is 1 or 'a', but it fails with an error if the value is NULL. Let’s discuss why and how to solve the problem.
Problem example
Consider the following query:
SELECT 1 AS a, -1 AS b, 'abc' AS c FROM dummy_table;It works. So it would be reasonable to think that this query will also work:
SELECT column1, NULL AS column2 FROM some_table;But this query will fail with the infamous error:
201: A syntax error has occurred.How solution
The reason why the query fails is that every column returned by a query must have a type. But NULL, when specified as a literal as we did in the example, has no type.
One could think that NULL should never have a value because it represents (according to one interpretation) an absence of values. But this is not correct in SQL, since columns always have a type and disregarding their type they can contain NULL. So we need to assign a value to the returned column! There are a couple of ways to do so.
Casting the value type
The following syntaxes are equivalent, and they both will serve the purpose:
SELECT CAST(NULL AS INT) AS x FROM t;
SELECT NULL::INT AS x FROM t;Use a variable
This is preferable if you want to add some logic that could change the variable value before the query:
DEFINE var1 INT;
LET var1 = NULL;
SELECT var1 FROM t;Reference
Informix documentation: