Re: NULLS in union of two tables

Hai

    Below Query perfectly works with version 7.3.4. I created view with below Union statement and NULL embedded in selects then It created view without giving any errors.

Wishes
Suresh

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 statements 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.
>
> Any suggestions?
>
> Thanks,
>
> Julie
Received on Thu Jun 04 1998 - 01:56:49 CDT