creating VIEW of 2 tables
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