NULLS in UNION of two tables

From: Julie A Peronto <jperont_at_entergy.com>
Date: Mon, 01 Jun 1998 15:04:06 -0500
Message-ID: <35730936.4CD4B07_at_entergy.com>

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 Mon Jun 01 1998 - 22:04:06 CEST