Re: NULLS in union of two tables

In article <6l1bd8$bqo_at_scorpion.LaTech.edu>,   jperonto_at_engr.latech.edu (Julie A. Peronto) wrote:
>
>
> Nuno Guerreiro (nuno-v-guerreiro_at_telecom.pt) wrote:
> : On 2 Jun 1998 14:26:43 GMT, jperonto_at_engr.latech.edu (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 believe what you need to perform is a JOIN, not a UNION. I think the
> : following command will create the view you need:
>
> : CREATE VIEW v1 AS
> : SELECTa.wr_no, a.wr_type, b.wr_status
> : FROM table_1 a, table_2 b
> : WHERE a.wr_no=b.wr_no;
>
> : Hope this helps
>
> : Nuno Guerreiro
> :
>
> 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
>

I think the following will work:

CREATE VIEW v1 AS
  SELECT wr_no, wr_type, null wr_status   FROM table_1
  UNIOIN ALL
  SELECT wr_no, null wr_type, wr_status   FROM table_2;

If you get an error message about the datatypes not matching, use the to_char function around the null. Also not that if you want to order this result set, you can only use column numbers. Column names won't work even though in the example above they are the same in both result sets.

Good luck.

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Wed Jun 03 1998 - 14:27:21 CDT