Hi All,

It seems it is not possible to select NULL from a table?
For example:

SELECT null FROM t

produces

SQL0206N "NULL" is not valid in the context where it is used. SQLSTATE=42703

Can that be true? I can select any other constant but not NULL. I can workaround it
by using the scalar function NULLIF(). Fx

SELECT NULLIF(0,0) FROM T

But that is very bad way of doing it and probably has performance impact.

Is there any good explanantion for all this?

Thanks in advance.

Alex.

cast(null as int)

Perhaps what you want to get is

SELECT COL1, COL2,.... WHERE COLX IS NULL

I really wanted to get a constant value of NULL for one or more columns. That is useful
for example when I use UNION. For example

( SELECT a.id, b.id, NULL FROM a JOIN b ON a.id = b.a_id )
UNION ALL
( SELECT a.id, NULL, c.id FROM a JOIN c ON a.id = c.a_id)

But instead of typing NULL directly I have to use CAST(NULL as INT) ...

Why would you need a NULL value? In your example any 'default' would do. If it were an integer field for example, I'd use somthing like -1 as a default 'ID unknown'.

Peter

This is good workaround Of course your default value has to be outside of the range of the normal values for that attribute. Also the application has to check for that default value instead of NULL . NULL is of course more natural choise, but is quite unpleasant to have to cast it to a specific type.

Yes, you need to keep the ID range in mind. I normally use -1 for 'unknown' and -9 for 'deleted' and a valid ID range > 0 where ever possible when dealing with ID's, so I don't get confused or forget my choices.

Another reason not to use NULL values is that some functions respond in unexpected ways when confronted with NULL values. Sometimes I'd end up converting NULLs into something else further down the line anyway.

Peter

I sometimes use null as sathyaram_s suggested and sometimes 'default' value depending on sort I need. Null values are not sorted the same way as 'default' values. So don't ignore posibilities of using null in SQL.