Re: NULLS in UNION of two tables

Hi Julie,

The only thing you have to be sure of is that the data_types in the first query are matched by the second, forcing data-typing where necessary.

So taking your original union,

select wr_no, wr_type, NULL wr_status
from table_1
union
select wr_no, NULL wr_type, wr_status
from table_2

if wr_status is a varchar2 then your query becomes select wr_no, wr_type, '' wr_status
from table_1
union
select wr_no, NULL wr_type, wr_status
from table_2

if however your wr_status is a number then your query becomes

select wr_no, wr_type, to_number('') wr_status from table_1
union
select wr_no, NULL wr_type, wr_status
from table_2

Exactly the same rational is used in wr_status were a date.  

the labels in the second query are redundant as only the names applied to the first query are relevant, all matching between first set results and second set results being positional.

hope this is helpful,

regards

Rod Corderey
Lane Associates
Lane_Associates_at_Compuserve.com
http://www.Lane-Associates

Julie A Peronto wrote:
>
> I am creating a view that is the union of two tables that hold different
> information about like objects. There are some fields that exist in
> both tables & other fields that exist in one or the other table. The
> union will hold a combination of the two. For example, if the tables
> are called table_1 & table_2, then both table_1 & table_2 have a field
> called wr_no, table_1 has a field called wr_type, and table_2 has a
> field called wr_status. wr_type & wr_status are not related to each
> other in any way. I want to create a view that unions the two tables in
> such a way that the view has the fields wr_no, wr_type, wr_status.
>
> I have tried the following select statement in the creation of the view:
>
> select wr_no, wr_type, NULL wr_status
> from table_1
> union
> select wr_no, NULL wr_type, wr_status
> from table_2
>
> This does not work. I get an error on the datatype of the NULL in the
> first select statement (ORA-01790). If, however, I were to hardcode a
> value in place of the NULL in the first select, giving the following:
>
> select wr_no, wr_type, 'BAD' wr_status
> from table_1
> union
> select wr_no, NULL wr_type, wr_status
> from table_2
>
> then everything is fine. The NULL in the second select causes no
> problem. But, this will not work for my situation. I need to have a
> NULL in the first select statement.
>
> We are currently using Oracle version 7.1.4 but will soon go to 7.3.
>
> I would appreciate any help with this matter.
>
> Thanks,
>
> Julie
Received on Tue Jun 02 1998 - 16:43:26 CEST