Object Tables   «Prev 

Alternate query example in Oracle

SELECT column_name_1, <column_name_2>
FROM table_name alis
WHERE alias.object_name.attribute_name = variable;

  1. Reference 1: The SELECT clause for selecting attributes from the object table
  2. Reference 2: The FROM clause for specifying the name of the object table
  3. Reference 3: The WHERE clause, which drills down to the attribute of the associated object

The same process of creating a view can be used with groups. The difference is that each row will contain information about a group of rows, which is analogous to a subtotal table.
For example, consider this group query:
select CategoryName, COUNT(*)
from BOOKSHELF
group by CategoryName;

You can create a view based on this query, and you can then query the view:
create or replace view CATEGORY_COUNT as
select CategoryName, COUNT(*) AS Counter
from BOOKSHELF
group by CategoryName;

Because the COUNT(*) column is a function, you have to give it a column alias (in this case, Counter) when using the query as the basis for a view.

Renaming Columns with Aliases

Notice the name Counter in the select clause. The AS Counter clause renames the column it follows. The new names are called aliases, because they are used to disguise the real names of the underlying columns (which are complicated because they have functions). When you query the view, you can (and must) now use the new column names:

select CategoryName, Counter from CATEGORY_COUNT;

The column "Counter" is referred to as a column alias, another name to use when referring to a column. In the description of the view, and in the query, there is no evidence of the grouping function performed, just the Counter column name. It is as if the view CATEGORY_COUNT were a real table with rows of monthly sums. Why?
Oracle automatically takes a single word, without quotes, and uses it to rename the column the word follows. When it does this, Oracle forces the word (the alias) into uppercase, regardless of how it was typed. You can see evidence of this by comparing the column names in the create view and the describe commands. When creating a view, never put double quotes around your column aliases. Always leave aliases in create view statements without quotes. This will cause them to be stored in uppercase, which is required for Oracle to find them.

Aliases in View Creation

Internally, Oracle works with all column and table names in uppercase. This is how they are stored in its data dictionary, and this is how it always expects them to be. When aliases are typed to create a view, they should always be naked, that is without quotation marks around them. Putting double quotation marks around an alias can force the column name stored internally by Oracle to be in mixed case. If you do this, Oracle will not be able to find the column when you execute a select unless you enclose the column name within quotes during all your queries. Never use double quotation marks in creating aliases for a view.