How do I return NULL from a select (union)?

[First Lastname]

I know that I must be missing something really obvious, but I am having
problems returning an explicit NULL from a select statement.

For example:

select Column1, NULL as Alias1 from table1
union
select Column1, Column2 as Alias1 from table2

results in the following error message:

"NULL " is not a column in an inserted table, updated table, or any
table identified in a FROM clause

I could resort to using the NULLIF function, but that seems sort of dirty.
Surely I am just not supplying the correct syntax.

Thanks in advance for any help,
Jeff Hartley

[S. J. Cross]

I believe you need to CAST the null to the data type of Column2 from Table2.

First Lastname <first.l...@worldspan.com> wrote in message
news:7ur19a$c4q$1...@ssauraaa-i-1.production.compuserve.com...

[Jeff Hartley]

Thanks for the reply S.J., but I think I was not adequately clear in my
post. The union is just an example and is not the only situation where i
would like to return an explict NULL. The syntax "select column1, NULL as
alias1 from table1" seems logical since I can select a constant (or literal
string) by similar means (select column1, 'Stuff' as alias1, 5 as alias2
from table1).

Any ideas?

S. J. Cross <s...@nospam.ibm.net> wrote in message
news:38116...@news3.prserv.net...

[S. J. Cross]

The NULL value has no intrinsic data type, and there is no default, so you
need to supply a data type to define the result set. (When you put a
non-NULL literal in your SQL SELECT, the data type is implicit.) You cannot
specify NULL in the column list of your SELECT statement without casting it
to a specific data type.

If the data type of the null column is not important, you can replace the
NULL in your SELECT statement with a CAST of the NULL to an arbitrary data
type value: e.g., CAST(NULL as INTEGER). But if you are doing a join, the
data types of the corresponding columns in the different tables must, of
course, be compatible. Hope this helps/makes sense.

Jeff Hartley <jeff.h...@ga.prestige.net> wrote in message
news:s14sni...@corp.supernews.com...

[Serge Rielau]

Hi Jeff,

NULL is a somewhat special keyword, it can only occur in certain places.
The reason is that NULL is by definition untyped.
So you have to cast it:

CAST(NULL AS <type>) AS Alias1

Cheers
Serge

[Jeff Hartley]

Thanks for the reply, Serge. Is the CAST function new? I tried the
following statement in version 5 and recieved the following error

select cast(NULL as smallint) from sysibm.sysdummy1

SQL0199N The use of the reserved word "AS" following "" is not valid.
Expected tokens may include: ") , ". SQLSTATE=42601

Any ideas?

thanks,
jeff

[Serge Rielau]

Hi Jeff,

Uhm - maybe - "sysdummy1" smells like you are using a bigger box (DB2
AS/400 ?)
They might have added that later than DB2 UDB for Unix et al.

Cheers
Serge

[Jeff Hartley]

Serge,

I am using DB2 OS/390 5.1.1 Has anyone used the cast function with this
version?

thanks,
jeff

Serge Rielau <sri...@ca.ibm.com> wrote in message
news:3814B22D...@ca.ibm.com...

[Adam Płaszczyca]

First Lastname napisał(a):

>
> select Column1, NULL as Alias1 from table1
> union
> select Column1, Column2 as Alias1 from table2
>
> results in the following error message:
>
> "NULL " is not a column in an inserted table, updated table, or any
> table identified in a FROM clause

Try expression CAST(NULL AS <TYPE>) AS Alias1.

--
___________ (R)
/_ _______ Adam Płaszczyca (+48 601) 829697
___/ /_ ___ ul. Jagiellońska 62 m 120, 03-468 Warszawa
_______/ /_ IRC: _555, http://www.waw.pdi.net/~trzypion/
___________/ mail: _5...@irc.pl UIN: 4098313