Use Of NULL Keyword In DB2

cyber0ne

I have a UNION in a SELECT query on DB2 where the second half does not
have all the fields that the first half has. In order to match up the
fields, I am trying to do something akin to the following:

SELECT
FIELD1 F1
FIELD2 F2
FIELD3 F3
FROM
TABLE1
UNION ALL
SELECT
OLDFIELD1 F1
NULL F2
OLDFIELD2 F3
FROM
TABLE2

Note that I am attempting to "fill in the blank" by placing a NULL
value in the second SELECT in order to match up the columns. For
character/string columns, I can just use an empty string. But I am
trying to use NULL for integer, date, etc. columns. However, the
database is returning an error that the NULL keyword is not valid in
this context.

Any ideas how I can accomplish this task?
Regards,
David P. Donahue
dd******@ccs.neu.edu
http://www.cyber0ne.com

Nov 12 '05 #1

Bob Stearns

cyber0ne wrote:

I have a UNION in a SELECT query on DB2 where the second half does not
have all the fields that the first half has. In order to match up the
fields, I am trying to do something akin to the following:

SELECT
FIELD1 F1
FIELD2 F2
FIELD3 F3
FROM
TABLE1
UNION ALL
SELECT
OLDFIELD1 F1
NULL F2
OLDFIELD2 F3
FROM
TABLE2

Note that I am attempting to "fill in the blank" by placing a NULL
value in the second SELECT in order to match up the columns. For
character/string columns, I can just use an empty string. But I am
trying to use NULL for integer, date, etc. columns. However, the
database is returning an error that the NULL keyword is not valid in
this context.

Any ideas how I can accomplish this task?
Regards,
David P. Donahue
dd******@ccs.neu.edu
http://www.cyber0ne.com

Try CAST(NULL AS <correct-type-for-column>)

It worked when I had similar problems. Serge Rielau had a good
explanation for this, basically having to do with a lack of context to
give a <data type> to the value NULL.

Nov 12 '05 #2

cyber0ne

> Try CAST(NULL AS <correct-type-for-column>)

Seems to be working perfectly for my needs, thanks!
Regards,
David P. Donahue
dd******@ccs.neu.edu
http://www.cyber0ne.com

Nov 12 '05 #3

Knut Stolze

Bob Stearns wrote:

Try CAST(NULL AS <correct-type-for-column>)

It worked when I had similar problems. Serge Rielau had a good
explanation for this, basically having to do with a lack of context to
give a <data type> to the value NULL.

NULL is un-typed. So when DB2 sees the NULL in the SELECT-list, it doesn't
know its type and it doesn't know if it type would be compatible with the
type of the first portion of the UNION. Giving the NULL a type via the
CAST eliminates this issue.

--
Knut Stolze
Information Integration Development
IBM Germany / University of Jena

Nov 12 '05 #4