By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,021 Members | 912 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,021 IT Pros & Developers. It's quick & easy.

Use Of NULL Keyword In DB2

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
> 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

P: n/a
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.