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
ddonahue@ccs.neu.edu
http://www.cyber0ne.com

cyber0ne wrote:[color=blue]
> 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
> ddonahue@ccs.neu.edu
> http://www.cyber0ne.com
>[/color]
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.

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

Seems to be working perfectly for my needs, thanks!


Regards,
David P. Donahue
ddonahue@ccs.neu.edu
http://www.cyber0ne.com

Bob Stearns wrote:
[color=blue]
> 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.[/color]

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