Database Reference
In-Depth Information
This view shows how you can use grouping and functions in a view. In
addition, notice that column aliases are used as a way to give the columns
more appropriate names rather than using a column list in front of the
query. Expressions, such as the columns with functions or group functions
on them, must be given a valid name when the view is created.
Imagine that you need to know which artists have balances over $500
for any month after 2000. The following query simplifies the work required
by selecting from a view:
SELECT NAME, BALANCE, BILLING_MONTH
FROM ARTIST_MONTHLY_STATEMENT
WHERE BALANCE > 500
AND TO_DATE(BILLING_MONTH, 'MON/YY') > '31-DEC-2000'
ORDER BY BALANCE DESC;
Figure 19.4 shows the result of this query. The BILLING_MONTH
column is converted to a date in the WHERE clause. This is needed
because it was converted to a character field in the view. If you did not con-
vert it to a date, it would be compared as a character field (alphabetically)
when evaluating the WHERE clause.
19.3.3.2
Inline Subquery Views
Another example of a complex view is one that contains a subquery. Sub-
queries can be used in the SELECT, FROM, and WHERE clauses of a
query. A view based on a query with a subquery in any of these SQL com-
mand clause locations is valid.
CREATE VIEW CD_SONGS AS
SELECT M.MUSICCD_ID, M.TITLE, T.TRACK_SEQ_NO,
(SELECT TITLE FROM SONG S
WHERE T.SONG_ID = S.SONG_ID) SONG_TITLE
FROM MUSICCD M JOIN CDTRACK T
ON (M.MUSICCD_ID = T.MUSICCD_ID);
The following script queries the view. Figure 19.5 shows the result.
COLUMN TITLE FORMAT A25
COLUMN SONG_TITLE FORMAT A40
SELECT TITLE, TRACK_SEQ_NO, SONG_TITLE FROM CD_SONGS
