create table T1 (
CUSTOMERID number not null constraint t1_pk primary key,
NAME varchar2(100),
EMAIL varchar2(80)
)
Table created.
create table T2 (
CUSTOMERID number not null,
ADDRID number not null,
ACTIVE varchar2(1) not null
)
Table created.
alter table T2 add constraint t2_pk primary key (CUSTOMERID, ADDRID)
Table altered.
create table T3 (
ADDRID number not null constraint t3_pk primary key,
ADDRESS varchar2(300),
COUNTRY varchar2(20)
)
Table created.
INSERT INTO T1 values (1,'Susan Oracle','soracle@something.com')
1 row(s) inserted.
INSERT INTO T1 values (2,'Robert Oracle','roracle@something.com')
1 row(s) inserted.
INSERT INTO T3 values (1,'Road 1','US')
1 row(s) inserted.
INSERT INTO T3 values (2,'Road 2','CA')
1 row(s) inserted.
select * from t3
ADDRID | ADDRESS | COUNTRY | 1 | Road 1 | US | 2 | Road 2 | CA |
---|
INSERT INTO T2 values (1,1,'Y')
1 row(s) inserted.
INSERT INTO T2 values (1,2,'N')
1 row(s) inserted.
select * from t2
CUSTOMERID | ADDRID | ACTIVE | 1 | 1 | Y | 1 | 2 | N |
---|
INSERT INTO T3 values (3,'Street 1','US')
1 row(s) inserted.
INSERT INTO T3 values (4,'Street 2','US')
1 row(s) inserted.
INSERT INTO T2 values (2,3,'N')
1 row(s) inserted.
INSERT INTO T2 values (2,4,'N')
1 row(s) inserted.
CREATE VIEW vw1 AS
WITH addr as ( select t2.customerid,
t3.address,
t3.country
from t2,t3
where t2.active = 'Y'
and t2.addrid = t3.addrid )
SELECT t1.name,
t1.email,
addr.address,
addr.country
FROM t1,
addr
WHERE addr.customerid (+) = t1.customerid
View created.
select * from vw1
NAME | ADDRESS | COUNTRY | Susan Oracle | soracle@something.com | Road 1 | US | Robert Oracle | roracle@something.com | - | - |
---|