469,609 Members | 1,652 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,609 developers. It's quick & easy.

Use Of NULL Keyword In DB2

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
3 8955
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
> 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
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

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

69 posts views Thread by Ken | last post: by
10 posts views Thread by Python_it | last post: by
102 posts views Thread by junky_fellow | last post: by
41 posts views Thread by Alexei A. Frounze | last post: by
20 posts views Thread by asdf | last post: by
5 posts views Thread by stmfc | last post: by
8 posts views Thread by COHENMARVIN | last post: by
51 posts views Thread by muktipada | last post: by
reply views Thread by Solution2021 | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.