Re: NULLS in union of two tables

On 2 Jun 1998 17:05:44 GMT, jperonto_at_engr.latech.edu (Julie A. Peronto) wrote:

>
>Thanks for the response, but I guess I should have been more clear.
>While the information in the tables pertains to like objects, the tables
>are mutually exclusive in those objects. The view will bring together
>all information about all objects. Going on my previous example, if the
>data in the two tables were as follows:
>
>table_1 table_2
>wr_no wr_type wr_no wr_status
>123 'WORK' 234 'GOOD'
>
>I would want the resulting view to be:
>
>VIEW
>wr_no wr_type wr_status
>123 'WORK' NULL
>234 NULL 'GOOD'
>
>
>Both selects require a NULL value as a placeholder for
>information not in the one table but in the other table, leading to my
>original posted problem.
>
>Julie

SQL> create view v1 as
  2 select b.wr_no, a.wr_type, b.wr_status   3 from table_1 a, table_2 b
  4 where a.wr_no(+)=b.wr_no
  5 union
  6 select a.wr_no, a.wr_type, b.wr_status   7 from table_1 a, table_2 b
  8 where a.wr_no=b.wr_no(+);

View created.

SQL> select * from v1;

    WR_NO WR_TYPE WR_STATUS

--------- -------------------- --------------------
      123 WORK
      234                      GOOD

Hope this helps,

Nuno Guerreiro Received on Wed Jun 03 1998 - 04:14:34 CDT