Creating a view that joins tables : Filter View « View « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. View
3. Filter View

SQL>
SQL> CREATE TABLE product_order (
  2       product_name  VARCHAR2(25),
  3       salesperson   VARCHAR2(3),
  4       order_date DATE,
  5       quantity      NUMBER(4,2)
  6       );

Table created.

SQL>
SQL>
SQL> CREATE TABLE person (
  2       person_code VARCHAR2(3) PRIMARY KEY,
  3       first_name  VARCHAR2(15),
  4       last_name   VARCHAR2(20),
  5       hire_date   DATE
  6       );

Table created.

SQL>
SQL>
SQL> INSERT INTO person VALUES ('CA', 'Chase', 'At', '01-FEB-02');

1 row created.

SQL> INSERT INTO person VALUES ('GA', 'Gary', 'Talor', '15-FEB-02');

1 row created.

SQL> INSERT INTO person VALUES ('BB', 'Bob', 'Bark', '28-FEB-02');

1 row created.

SQL> INSERT INTO person VALUES ('LB', 'Laren', 'Baby', '01-MAR-02');

1 row created.

SQL> INSERT INTO person VALUES ('LN', 'Linda', 'Norman', '01-JUN-03');

1 row created.

SQL>
SQL>
SQL> INSERT INTO product_order VALUES ('Product 1', 'CA', '14-JUL-03', 1);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 2', 'BB', '14-JUL-03', 75);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 3', 'GA', '14-JUL-03', 2);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 4', 'GA', '15-JUL-03', 8);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 5', 'LB', '15-JUL-03', 20);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 6', 'CA', '16-JUL-03', 5);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 7', 'CA', '17-JUL-03', 1);

1 row created.

SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE VIEW sales_per_person_v AS
  2  SELECT pers.first_name || ' ' || pers.last_name SALESPERSON,
  3         purc.product_name,
  4         purc.order_date,
  5         purc.quantity
  6  FROM   person   pers,
  7         product_order purc
  8  WHERE  pers.person_code = purc.salesperson (+);

View created.

SQL>
SQL> SELECT * FROM sales_per_person_v
  2  ORDER BY salesperson, product_name, order_date;

SALESPERSON                          PRODUCT_NAME              ORDER_DAT   QUANTITY
------------------------------------ ------------------------- --------- ----------
Bob Bark                             Product 2                 14-JUL-03         75
Chase At                             Product 1                 14-JUL-03          1
Chase At                             Product 6                 16-JUL-03          5
Chase At                             Product 7                 17-JUL-03          1
Gary Talor                           Product 3                 14-JUL-03          2
Gary Talor                           Product 4                 15-JUL-03          8
Laren Baby                           Product 5                 15-JUL-03         20
Linda Norman

8 rows selected.

SQL>
SQL> drop table product_order;

Table dropped.

SQL> drop table person;

Table dropped.