Re: creating VIEW of 2 tables
Dan
How about this strategy?
CREATE VIEW v_cust1 (tableid, name, address, phone) AS
SELECT 1, name, address, phone
FROM cust1;
CREATE VIEW v_cust2 (tableid, name, address, phone) AS
SELECT 2, name, address, phone
FROM cust2;
SELECT name, address, phone FROM v_cust1
UNION
SELECT name, address, phone FROM v_cust2;
You will need to create 2 views with the table id (1 and 2 to denote
cust1 and cust2) and then UNION the views.
HTH
Sujit Pal
______________________________ Reply Separator
_________________________________
Subject: creating VIEW of 2 tables
Author: dant@rose.hp.com (Dan Thibadeau) at Internet
Date: 2/24/98 11:57 PM
Since I can't use UNION in a VIEW, is there another way to accomplish
the following:
- I have the following two tables:
cust_1 (100k+ rows) cust_2 (5k+ rows)
name name
addr addr
phone phone
data1
data2
data3
- customers in cust_1 do NOT exist in cust_2
- I want the VIEW:
customers (should contain 105k+ rows)
name
addr
phone
The closest idea I have (it doesn't work) is to create a Cartisian Product
to a "bogus" table (1 row and 1 field), and to have an SPL filter the
output:
CREATE PROCEDURE notnull (str1 CHAR(20), str2 CHAR(20))
RETURNING CHAR(20) ; IF str1 != '' THEN
RETURN str1 ;
ELSE
RETURN str2 ;
END IF
END PROCEDURE ;
CREATE VIEW AS customers
SELECT notnull(c1.name, c2.name) name,
notnull(c1.addr, c2.addr) addr,
notnull(c1.phone, c2.phone) phone
FROM bogus, OUTER cust_1 c1, OUTER cust_2 c2
Thank,
Dan Thibadeau