OracleBlog

I would like to elaborate on one point from my recent blog on Using Views:

http://thinkoracle.blogspot.com/2005/07/use-views.html

The example in question is a recent case where I used Views to very easily perform a complex query.

Let's set it up. We have a simple company with expenses and revenues broken up by type and department. I'm ignoring many columns/constraints and the expensetype table for simplicity.

CREATE TABLE department (
dept_name VARCHAR2(32) PRIMARY KEY,
dept_description VARCHAR2(64)
);

CREATE TABLE expenses (
dept_name REFERENCES department(dept_name),
expense_type VARCHAR2(32),
expense_description VARCHAR2(64),
expense_amount NUMBER(10,2)
);

CREATE TABLE revenues (
dept_name REFERENCES department(dept_name),
revenue_type VARCHAR2(32),
revenue_description VARCHAR2(64),
revenue_amount NUMBER(10,2)
);

Okay now I'd like to insert a little sample data so we can test some queries

INSERT INTO department VALUES ('DEPT_A', 'Expenses Only');
INSERT INTO department VALUES ('DEPT_B', 'Revenues Only');
INSERT INTO department VALUES ('DEPT_C', 'Expenses and Revenues');
INSERT INTO department VALUES ('DEPT_D', 'No Expenses Nor Revenues');

INSERT INTO expenses VALUES ('DEPT_A', 'TYPE_1', 'Expense 1', 10.00);
INSERT INTO expenses VALUES ('DEPT_A', 'TYPE_2', 'Expense 2', 12.50);
INSERT INTO expenses VALUES ('DEPT_C', 'TYPE_1', 'Expense 3', 44.90);
INSERT INTO expenses VALUES ('DEPT_C', 'TYPE_3', 'Expense 4', 92.75);

INSERT INTO revenues VALUES ('DEPT_B', 'TYPE_1', 'Revenue 1', 14.60);
INSERT INTO revenues VALUES ('DEPT_B', 'TYPE_1', 'Revenue 2', 15.80);
INSERT INTO revenues VALUES ('DEPT_C', 'TYPE_4', 'Revenue 3', 47.75);
INSERT INTO revenues VALUES ('DEPT_C', 'TYPE_5', 'Revenue 4', 6.15);

We want a query that will show us every department, and its total expenses and revenues. We want dept_name, sum(expenses), sum(revenues), regardless of type.

Doing a single sum would be easy, we could just "GROUP BY" a particular column. Even if we wanted to add dept_description, we could just use Connor McDonald's trick:

http://thinkoracle.blogspot.com/2005/07/extra-columns-in-group-by.html

For simplicity, by the way, we won't include dept_description - we know we can using that technique.

Go ahead and write the query. I'm sure its possible! But not everyone can figure it out, you might wind up with something really complex.

So how will views help us? Well, we can create views that give us the sum for expenses and values:

CREATE VIEW expensesview AS 
SELECT dept_name, sum(expense_amount) expense_sum
FROM expenses
GROUP BY dept_name;

CREATE VIEW revenuesview AS
SELECT dept_name, sum(revenue_amount) revenue_sum
FROM revenues
GROUP BY dept_name;

Nothing could be easier. That is also some pretty handy views to have. As you recall, a view can be thought of as a "virtual table" or as a stored query. A stored query we are about to put to use.

SELECT d.dept_name, e.expense_sum, r.revenue_sum
FROM department d, expensesview e, revenuesview r
WHERE d.dept_name = e.dept_name 
AND d.dept_name = r.dept_name;

Here are the results ... oops!

DEPT_NAME                        EXPENSE_SUM REVENUE_SUM
-------------------------------- ----------- -----------
DEPT_C                                137.65        53.9

Why did we only get DEPT_C? Because, of course, either expense_sum or revenue_sum was NULL in the other 3 departments. I'm only showing this mistake so I can show the application of an outer join. An outer join will give you all possible rows. Just add a (+) next to the column you're joining on. Symbolically that means you want to add (+) rows where none exist.

SELECT d.dept_name, e.expense_sum, r.revenue_sum
FROM department d, expensesview e, revenuesview r
WHERE d.dept_name = e.dept_name(+) 
AND d.dept_name = r.dept_name(+);

DEPT_NAME                        EXPENSE_SUM REVENUE_SUM
-------------------------------- ----------- -----------
DEPT_A                                  22.5
DEPT_B                                              30.4
DEPT_C                                137.65        53.9
DEPT_D

Perfect.

We could have done this without views. Indeed, we could have embedded the simple queries we used to make the views directly into this final query. Maybe in an example as simple as this that would be ok. But these kinds of things can get complex. Plus, now other queries can take advantage of those views.

As a final note, how do you figure we can replace the NULLs up there with zeros? The answer is something like DECODE or NVL.

http://thinkoracle.blogspot.com/2005/06/nulls-in-oracle.html

SELECT d.dept_name, 
NVL(e.expense_sum,0) tot_expense, 
NVL(r.revenue_sum,0) tot_revenue
FROM department d, expensesview e, revenuesview r
WHERE d.dept_name = e.dept_name(+) 
AND d.dept_name = r.dept_name(+);

DEPT_NAME                        TOT_EXPENSE TOT_REVENUE
-------------------------------- ----------- -----------
DEPT_A                                  22.5           0
DEPT_B                                     0        30.4
DEPT_C                                137.65        53.9
DEPT_D                                     0           0